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

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.

[ Description = "Sharepoint List URL" ] SPListURL = null meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true];

[ Description = "Sharepoint List ID" ] ListID = null meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true];

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.

#"SharepointList IDs" = let
    Source = SharePoint.Tables(SPListURL, [ApiVersion = 15])
in
    Source;

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

 SPList = let
//SPListURL is the URL of sharepoint list
Source = SharePoint.Tables(SPListURL, [ApiVersion = 15]),
/* ListID is the ID of Sharepoint list */
SPList = Source{[Id=ListID]}[Items],
#"Keep only FieldValuesAsText column" = Table.SelectColumns(SPList,{"FieldValuesAsText"}),
RecordColumnNames= Record.FieldNames(#"Keep only FieldValuesAsText column"{0}[FieldValuesAsText]),
ExpandColumn= Table.ExpandRecordColumn(#"Keep only FieldValuesAsText column", "FieldValuesAsText",RecordColumnNames)
in
ExpandColumn;

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.

 ListColumnNames = let
    Source = SPList,
    ColumnsNames= Table.ColumnNames(Source ),
    #"Converted to Table" = Table.FromList(ColumnsNames, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table";

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 🙂

 Title = let
    //SPListURL is the URL of sharepoint list
    Source = SharePoint.Tables(SPListURL, [ApiVersion = 15]),
    /*    ListID is the ID of Sharepoint list       */
    SPList = Source{[Id=ListID]}[Items],
    #"Removed Other Columns" = Table.SelectColumns(SPList,{"ContentType"}),
    ContentType1 = #"Removed Other Columns"{0}[ContentType],
    Fields = ContentType1[Fields],
    #"Removed Other Columns1" = Table.SelectColumns(Fields,{"EntityPropertyName", "InternalName", "StaticName", "Title"})
in
    #"Removed Other Columns1";

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.

RenamedColumns = let
    Source = Table.NestedJoin(ListColumnNames,{"Column1"},Title,{"EntityPropertyName"},"Title",JoinKind.LeftOuter),
    #"Expanded Title" = Table.ExpandTableColumn(Source, "Title", {"EntityPropertyName", "Title"}, {"EntityPropertyName", "Title.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Title",{{"Column1", "Original Name"}, {"Title.1", "Title"}}),
    #"Filtered Rows" = Table.SelectRows(#"Renamed Columns", each ([EntityPropertyName] <> null)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Original Name", "Title"}),
    AddListColumns= Table.AddColumn(#"Removed Other Columns", "Custom", each Table.ColumnNames(SPList)),
    CheckNameAlreadyExists = Table.AddColumn(AddListColumns,"TF",each List.Contains([Custom],[Title])),
    #"Filter already existing column names" = Table.SelectRows(CheckNameAlreadyExists, each [TF] = false),
    #"Removed Duplicates" = Table.Distinct(#"Filter already existing column names", {"Title"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Removed Duplicates",{"Original Name", "Title"}),
    MakeAList= Table.TransformRows(#"Removed Other Columns1", Record.FieldValues)
in 

    MakeAList;

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.

 #"Sharepoint List" = let
    Source = SPList,
    RenameColumns= Table.RenameColumns(Source,RenamedColumns,MissingField.UseNull)
in
    RenameColumns;

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 🙂