David's Opinions

Power BI – SharePoint List 2.0 Beta connector 9 November 2020

#PowerBI #PowerQuery #M

The October 2020 release of Power BI includes the new SharePoint List 2.0 Beta connector. Keen to try it out I had a go at upgrading my imports of existing lists to the new connector.

I was able to, in most cases, update the references to the SharePoint List by editing one line in the Power Query code.
From this:
Source = SharePoint.Tables(“https://xxxx.sharepoint.com/sites/MyList”, [ApiVersion = 15]),
To this:
Source = SharePoint.Tables(“https://xxxx.sharepoint.com/sites/MyList/”, [Implementation=”2.0″, ViewMode=”All”]),

That was the easy part. The good news is that the import/load is now much cleaner and it brings in renamed columns as their new names.
I struct a particular challenge with the “People” type column definition and with the field being partially populated – for example we have 50 customers in the list and only three of them have an assigned or populated “Service Manager”. So extracting those details, which are there, became particularly difficult.

The solution turned out to be a custom line of Power Query code. I used the community forums and twitter to get the answer… and it consumed a lot of time. Here’s the line of code:

= Table.TransformColumns(#"Added Custom1", {{"Custom", each if Value.Is(,type list) then {0} else [title = "not assigned"], type record}} )

Annoyingly the “secret sauce” is _{0}

More details can be found in the community post discussion on this: https://community.powerbi.com/t5/Power-Query/Extracting-Title-from-SharePoint-Online-list/td-p/1475340

On the journey I found some Power Query (M) blog posts – totally awesome…
https://ssbi-blog.de/blog/technical-topics-english/lists-in-power-query-how-when-and-why/
https://ssbi-blog.de/blog/technical-topics-english/records-in-power-query-how-when-and-why/
https://bengribaudo.com/blog/2018/09/13/4617/power-query-m-primer-part9-types-logical-null-binary

Enjoy.