Power App Building with SharePoint List as data source – Part 1(of 3)

Caveat – the target audience for this post are those who are not well versed in setting up SharePoint/SharePoint Lists.

I recently gave a presentation where I built a Power App using SharePoint Lists as the data source. For this post I thought I would walk through, step by step, how I set up the SharePoint List. I will follow up with a second article using this SharePoint List as data source for a Power App.

In a previous article I used Excel Online as the data source to build a Power App ( Citizen Developer Journey - My First "Power Apps" App | LinkedIn). And while Excel/Excel Online are good data sources to leverage as you learn Power Apps, the use of SharePoint/SharePoint Online provides a bit more functionality as well as increased data source security. To be clear, I am not a SharePoint Expert – I am learning with you. This SharePoint List will hold data related to community activity: activity type (blog posts, vlog post, forum participation, speaking engagement, etc.); title of the activity; date of the activity; event name (conference name, hosted on YouTube, blog post, forum name), number of attendees or content views. With that, lets walk through how I set up a SharePoint List as a data source for a Power App.

Below I have opened my test SharePoint site and have clicked on the +New dropdown list. Of the options I will click on List which opens a Create List window. SharePoint offers numerous options including template List options – select Blank List. 

Once you click on Blank List SharePoint renders a window asking you to name your List as well as provides a field to Describe your newly created list. The naming of the List is mandatory while the description is not. Below is the new SharePoint List I created named Community Activity 2. This is also listed in the column on the left-hand side of the page. 

Similar to building a table in Excel you will need to name columns for the data you are collecting. SharePoint Lists provides you (the maker) several different types of data fields as referenced below. My suggestion is that you play around with each option to become familiar. For this SharePoint List the first column will host event date information. I selected the Date and Time option. 

Once the Date and Time option is selected a window opens asking the maker to provide the column name which I used “EventDate”. The window also confirms the type of column selected (Date and Time), provides a column Description field, and provides options whether you want event time shown – I selected no in this case. Once the column is configured to your needs click Save. 

The next column will hold names of various activities - titled Activity. To keep naming convention as well as spelling consistent SharePoint Lists provides the ability to create a dropdown list through the Choice type of column. The Choice column type allows you to easily prepopulate options for your users. And you can change/edit choices after the SharePoint List has been saved. I will show how a bit later. 

Next column will house the name of the event – titled EventName. This column type will be defined as Single line of text. 

This column will hold the event attendees or content views of each community activity line item. Through the Add Column dropdown we’ll select Number type naming the column AttendeesViews. SharePoint provide you options to define the decimal places of the number or show the number as a percentage. 

The final column will be a Lookup column and something we will leverage during the Power App building process. As this column is a Lookup type of column, we need to build another SharePoint List that will provide the reference list for the Lookup functionality. This will be based on the type of community activity as referenced in Activity column. Therefore, I built a single column SharePoint List using the same naming convention of the Activity column – this List is titled Activities 2. This may be a bit confusing but hopefully my upcoming explanation will bring this full circle.

Activity2 SharePoint List:

Back to our Community Activity 2 SharePoint List and the building of the Lookup column. We will click on the +Add column dropdown and select More. 

Upon selecting More the screen below will render providing you more options to configure the column. We will name the column CommunityWork. Then we will want to click on Lookup (1). Then we need to associate this column with the Activities2 SharePoint List. To do this go to Get Information From area (2), click on the dropdown box and choose Activities2.  

Now that our SharePoint List is set up let’s add some data. Click on +New to add a new activity entry. The screen shot below shows the Activity column dropdown list of Blog Post, Vlog, Speaking Engagement to choose from. 

Next screen shot I have clicked on CommunityWork to add the type of activity – remember that this is a LookUp type column tied to the Activities2 SharePoint List. As can you see the drop list rendered is just numbers. This is an error due to my not identifying the column name (from the Activities2 SharePoint List). This can easily be corrected. 

The following slides walk through the editing process for this column.

Step 1: Within the SharePoint List click on the Gear icon in the upper right-hand corner of page – this will open Setting for the page. Then click on List Settings.

Step 2: In the Settings window click on CommunityWork – this is listed in the Columns section. This will open the Settings window for that column.

Step 3: In the Settings window for the CommunityWork column you can make edits. In this window we want to direct the Lookup feature of this column to identify a specific column from the Activities2 SharePoint List and pull information into this (the CommunityWork) column. To do this find the text “In this column” and click on the dropdown box. The dropdown box lists the columns names within the Activities2 SharePoint List – click on “Title”. Then click Ok in the bottom right of the window.

Step 4: Now we have the list from the Activities2 SharePoint List associated to the LookUp functionality within the CommunityWork column of the Community Activity 2 SharePoint List. This will be important to leverage when we build our Power App. 

Now that the SharePoint List is set-up, I suggest you prepopulate with a few entries so that we will have some data to work with when we build the Power App – which will be covered in the next article. If you have any questions/concerns, please share in the comments or message me directly. My responses may be slow, but I try very hard to respond to all.

NOTE: I’d like to thank my colleague April Dunnam for sharing a bit of her SharePoint expertise.