Sparsely Populated SharePoint Complex Columns and Power BI

When we use SharePoint lists as data sources for Power BI, there are all sorts of tricks and gotchas. As with any rapidly evolving technology, some of the tricks we find on the InterWebs are helpful, some are out or date, and some are just plain wrong.

Recently I ran into a confusing issue when I was trying to use a Person or Group column in a Power BI report using the Power BI Desktop app. This could be an issue for any complex column from a SharePoint list that’s sparsely populated, though. Complex columns might include:

• Person or Group
• Manage Metadata
• Lookup
• Location

The reason they are complex is that they actually contain an object, not just a simple value like a Single line of text column, for example.

Expanding Complex Columns

After you’ve added a SharePoint list as a data source for your model in Power BI (doing this is well documented elsewhere), you generally will need to manipulate the resulting table so you can make use of all the columns, especially the complex columns. You can do this in the Power Query Editor.

Depending on which type of column you’re looking at, you may see List or Record in the Power Query Editor.

Person or Group columns will display as a List.

Managed Metadata columns will display as Record.

Notice in the screenshots above, you see the cool double arrow icon to the right of the column name. That means the column can be expanded to show properties of the underlying object.

Here’s what that process looks like for the Created By column. After I click on the double arrow icon, I have the choice to Expand to New Rows or Extract Values…. I choose Expand to New Rows .

Then I see Record instead of List. Why is that? Well, a Person or Group column is stored in an array when we get it through the connection – thus a List. First we flatten the array (there is only one value for Created By) and then we have what’s known as a Record.

When we click the double arrow again, we can choose which properties we want to extract from the Record. I’d recommend you only extract the values you actually want to use. In this case, it might be just be the title (which means the person’s name) for the Person or Group column.

Once we’ve made that selection, we end up with a new column (if you’ve taken the defaults), In this case, the new column is called CreatedBy.title. The great thing is it contains the thing we actually want to know: in this case, the person who created the item.

You can choose to rename the column if you’d like and do other things with it from here.

Sparsely Populated Issue

All the info above is just background. My issue was that I didn’t see the double arrows for some of the columns I knew were complex columns where I expected to see Record or List.

Here’s an example. Modified By looked the way I wanted it, but Referral Employee didn’t.

The Referral Employee was a Person or Group column, just like Modified By was. So why wasn’t I seeing the option to expand it?

I couldn’t figure out why this was, so of course, I turned to Twitter.

In #PowerBI, why do I see the option to expand some #SharePoint 'Person or Group' columns in the Power Query Editor and not others? None are multi-select.

— Marc D Anderson (@sympmarc) October 18, 2021

The conversation was helpful, but it didn’t quite get me to a solution. Thanks to Treb Gatte (@tgatte) for jumping in to help. But it did give me some hints.

After some fiddling with the table, I tried to filter for only items which had a value in the Referral Employee column. (This was just by luck!) Once I did this, and I only saw items where there was a value in the Referral Employee column, the magical double arrow icon was there!

Fantastic! All in needed to do was follow the process I showed above for Created By, and I’d be all set!

Not so fast, smart guy. Once I did this and removed the filtering, I got this error.

OLE DB or ODBC error: [Expression.Error] We cannot convert the value "" to type Table..

Because the Referral Employee column is sparsely populated, many of the values are actually empty. If you’ve been using computers for a while, you may know that empty, blank, null – and some other words – get tossed around all the time. They seem the same, but they really aren’t.

The error above was caused by the fact that the Power Query Editor was trying to expand empty values into records. I suppose it’s not smart enough to deal with that, but I can also see it as a good thing since you might want different things to happen when this is the case.

As you’ve undoubtedly seen, when you transform the table, the steps you take are piled up in the Query Settings on the right.

Underneath those steps is real DAX code [I was wrong about this: it’s M code. Thanks for the correction, Ingeborg!], and the fix for this issue requires some alteration of that code.

To get to the code view of the steps, go to View / Advanced Editor.

For the steps above, the code looks like this – but without line 7. Line 7 contains the fix. (There are several posts in the Resources below which gave me info on how to do this.)

There are a few things you need to know about this code to make the changes we need. Each transformation step relies on the step above it. You’ll notice that the Power Query Editor gives each step a unique name, adding numbers at the end if required. For example, Expanded Client and Expanded Client1 (the two steps to expand this Lookup column).

Essentially what we want to ask Power BI to do is transform the values in the Referred Employee column which are empty into nulls.

To do this, I inserted line 7:

 #"Referral Employee Fixed" = Table.TransformColumns( #"Expanded Client1", {{"Referral Employee", each if Value.Is(_,type list) then _ else null}}), 

Notice that the first parameter in the Table.TransformColumns function is the name of the prior step, which is #"Expanded Client1". The second parameter is the function which makes the replacement of empty values with nulls.

The other change we need to make is to edit line 8 to then refer to the #"Referral Employee Fixed" step rather than the #"Expanded Client1" step.

Before code:

let
    Source = SharePoint.Tables("https://milestonefinancialplannin.sharepoint.com/sites/ClientsHub", [Implementation="2.0", ViewMode="All"]),
    #"3cb984e3-6600-4823-88dc-e42bdd081ec9" = Source{[Id="3cb984e3-6600-4823-88dc-e42bdd081ec9"]}[Items],
    #"Expanded Quarter" = Table.ExpandRecordColumn(#"3cb984e3-6600-4823-88dc-e42bdd081ec9", "Quarter", {"Label"}, {"Quarter.Label"}),
    #"Expanded Client" = Table.ExpandListColumn(#"Expanded Quarter", "Client"),
    #"Expanded Client1" = Table.ExpandRecordColumn(#"Expanded Client", "Client", {"lookupId", "lookupValue"}, {"Client.lookupId", "Client.lookupValue"}),
    #"Expanded Referral Employee" = Table.ExpandListColumn(#"Expanded Client1", "Referral Employee"),
    #"Expanded Referral Employee1" = Table.ExpandRecordColumn(#"Expanded Referral Employee", "Referral Employee", {"id", "title"}, {"Referral Employee.id", "Referral Employee.title"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Referral Employee1",{{"Referral Credit", Currency.Type}, {"Revenue", Currency.Type}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Quarter_ClientRating", each Text.Combine({[Quarter.Label], "_", [Client Rating]}), type text)
in
    #"Inserted Merged Column"

After code:

let
    Source = SharePoint.Tables("https://tenantname.sharepoint.com/sites/ClientsHub", [Implementation="2.0", ViewMode="All"]),
    #"3cb984e3-6600-4823-88dc-e42bdd081ec9" = Source{[Id="3cb984e3-6600-4823-88dc-e42bdd081ec9"]}[Items],
    #"Expanded Quarter" = Table.ExpandRecordColumn(#"3cb984e3-6600-4823-88dc-e42bdd081ec9", "Quarter", {"Label"}, {"Quarter.Label"}),
    #"Expanded Client" = Table.ExpandListColumn(#"Expanded Quarter", "Client"),
    #"Expanded Client1" = Table.ExpandRecordColumn(#"Expanded Client", "Client", {"lookupId", "lookupValue"}, {"Client.lookupId", "Client.lookupValue"}),
    #"Referral Employee Fixed" = Table.TransformColumns( #"Expanded Client1", {{"Referral Employee", each if Value.Is(_,type list) then _ else null}}),
    #"Expanded Referral Employee" = Table.ExpandListColumn(#"Referral Employee Fixed", "Referral Employee"),
    #"Expanded Referral Employee1" = Table.ExpandRecordColumn(#"Expanded Referral Employee", "Referral Employee", {"id", "title"}, {"Referral Employee.id", "Referral Employee.title"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Referral Employee1",{{"Referral Credit", Currency.Type}, {"Revenue", Currency.Type}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Quarter_ClientRating", each Text.Combine({[Quarter.Label], "_", [Client Rating]}), type text)
in
    #"Inserted Merged Column"

And the result is just what I want:

Yup, lots of nulls, but where there are values I see them – and no errors!

Resources

• Solved: SharePoint List and null values – Microsoft Power BI Community
• Solved: Re: Check for empty table before expandRecordColum… – Microsoft Power BI Community