Home Power BI SharePoint List Power BI Template

SharePoint List Power BI Template

by Prathy Kamasani November 9, 2017 0 comment

SharePoint is a killer; front end looks nice and beautiful. Once you start exploring behind the screens, that is when it shows the true colours. Whenever you view a list in the SharePoint mostly, you are looking at a SharePoint list View, so it does not show many underlined columns, not even columns with their real names. When you use SharePoint list as a source for a Power BI model, apparently it extracts all the information from the backend, which means you see far more columns than you expected and most of the times column names look obscure.

For example, look at following columns, how anyone can interpret s6hu meant to be column Jan-17

Original Name  Display Name
Title AI Number
cybf Aug-17
Notes1 Notes
s6hu Jan-17
a6ua Feb-17
dnxw Mar-17
uv0g Apr-17
yqkw May-17
ContentType Content Type

If you have faced this problem, want a quick fix, then just download this template. It should rename columns with display names wherever possible, or you may want to read rest of the post, you will know how I created the Template.

SharePoint List Power BI Template

Step 1:

First I created two parameters one for SharePoint URL and one for SharePoint list ID.

If you are not sure about getting List ID follow this URL or download this template – Get Sharepoint List ID’s. I used following M Code in the Get Sharepoint List ID’s template.

Bingo, you get all lists from the given SharePoint site.

Step 2:

Once I know the URL and ID, I created another query with following M Code

I navigated to the list I want to import data from, chose FieldValuesAsText column only. FieldValuesAsText has text representation of all list data. Then I used function Record.FieldNames to get all values from Record. This makes it easy for future proofing. If any user changes columns in the future, Model will be clever enough to know the new column names. Then Expanded FieldValuesAsText column with the RecordColumnNames list.

Step 3:

I referenced the previous query and this time extracted all available column names using function Table.ColumnNames. Later I am going to use this query to join the query from Step 4.

Step 4:

I created another query with the same source as step 1 but in this query, I navigated to field ContentType and then Fields. Apparently, this is where SharePoint hiding all its secrets 🙂

Step 5:

I think this step is where the important stuff is.

First, I did a left outer join with ListColumnNames (Step3) and Title (Step 4) on EntityPropertyName and Column1. Then expanded and removed unnecessary columns. I ended up with Origin Name and Title (Display Name name in the SharePoint list). Then I created a custom column with a List of original column names.

In step 6, I want to rename all columns based on the display columns, but if we have duplicate columns or if there is already a column with the same name, Table.RenameColumns function will fail.

So I created a custom column with a list of original column names to see if the column already exists. Then I used List.Contains to check if the column already exists and I filtered out all columns which already exists.

In the end, I left again with Original Name and Title but excluded duplicates and columns already exist in the SharePoint List, then I transformed rows to list using Table.TransformRows function.

Step 6:

The final step for the template!

Again I referenced my query from Step1, SPList. Then used function Table.RenameColumns to rename columns as display names.  That’s all! Your lovely SharePoint list is ready for the transformations and analysis.

Summary :

Lately, one slogan I have heard among people is “We are in digital transformation”. The first place they start or probably the easiest place to start is moving all important Excel workbooks to SharePoint Online then analyse them using Power BI. But it was not that simple to make it work, at least not for me. I could write stories about it. I hope I will find some time to write about my adventures of successfully scheduling a Power BI Model with SharePoint list data source…

Sometimes you face irritating problems, If a user changes a column, it breaks the model, because of renaming columns to friendly names as it looks like in the Sharepoint List. I can’t imagine the frustration of a user when a model brakes with a name change error. Luckily I did not work with really wide lists. But with the very first model I designed with SP list, I realised this issue. My best friend google betrayed me, and I left with no choice other than exploring all the metadata I extracted from Sharepoint List.

This Template helped me to speed up the process. Now I don’t need to create Models for users; if they want they can design their own models, the way they want. It’s a win win 🙂

It’s not only me; there are other are others had similar issues

https://community.powerbi.com/t5/Desktop/Column-Name-Changes-when-Sharepoint-list-is-imported-in-PBI/td-p/183814

https://community.powerbi.com/t5/Issues/Sharepoint-list-connector-pulling-in-incorrect-column-names/idi-p/138924

I hope this helps someone out there. This is just a workaround; it worked great for me, hope it works for you too 😊

Sharepoint Power BI Template  – https://1drv.ms/u/s!Avm7gbgZtlMlqCHE55X7yJaDuBx0

Until next time,

Prathy 🙂

 

0 comment

You may also like

Leave a Reply

%d bloggers like this:
Skip to toolbar