jump to navigation

Power BI – SharePoint List 2.0 Beta connector 9 November 2020

Posted by David Wilson in General.
Tags: , ,
trackback

#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.

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: