Creating a SharePoint List with Power BI in mind....
In this blog I'll cover how to create a SharePoint list (SPL) with the hope for it to be used it as data source within Power BI desktop.
I find it's best to use a SPL as a data source when:
1) I can convince business to switch from using excel, google docs..... post it notes!!! 2) there's no scope and or budget to build an application built to support the needs of the business
SPL Pros & Cons
- data validation and source control
- relatively easy to create
- can incorporate additional business logic by utilising Microsoft Flow
- not applicable to all scenarios (e.g. complex finance sheets with loads of formulas)
- time taken to cross examine the business user to ensure the SPL can and will serves it desired purpose
- you might end up managing the permissions for SPL
Here's some real world business situations where I've used SPL's as a data source:
- Ideas notice board
- Power BI end user requirement initial gathering
- Power BI project status (for each report)
Creating the SPL
To create the SPL click on the 'New'
button and then
select 'List' on the SharePoint site
you want to the SPL to appear on
Next name the SPL and provide a relevant description.
Well done you just created a SPL!!!!
Modifing the SPL
The next stage is to customise your SPL based on the business needs. The list settings page is where you can:
- create new columns
- modify columns already created
- modify the permissions for the SPL
- create views
- and many other things
Creating a column
When creating a column choosing the correct column and data validation type the first time around will save a lot of time in the long run.
Don't forget the end goal here is to end up with a list that is ready to be fed into the Power BI data model.
The easier we make for power query the better.
The column types I've found myself using the most are:
- Single line of text: This is best suited for when"freestyle" text is acceptable. - Choice: When there should only be specific options to the end user for e.g. Priority Level: High, Normal or Low.
- Date and Time: When you want to ensure only date and/or time is entered in a consistent format
- Lookup: If you have linked SPL then you ensure that it can be linked to another list (great for when you need to create a relationship in Power Bi later at a latter stage )
-Person or Group: Can be integrated with Microsoft flow for approvals, email notification and many other things
Thing to keep in mind
Things that can trip you up in the long term:
- Enabling 'Require that this column contains information'
- Enabling 'Fill-in choices' when you select choice column basically turns it into a 'Single line of text' column type. I generally would not recommend enabling 'Fill-in choices'.
Complete list screenshot
From something simple like the list below you could:
- create a Power BI report to stakeholder that will give them an insight into how the Power BI projects.
- trigger a Microsoft Flow to notify the email lead they a report assigned to them.
Hope this has given you a few use cases for SPL's. In future blogs I'll go show you can:
-connect to the data source using Power BI Desktop - set up permissions for the list
- link multiple lists
Thanks for the read, it took me a while to write...... CPU joke in there somewhere.