I created a simple Power BI report to pull data from a SharePoint list and quickly ran into a problem with the Person or Group column type.
Error:
Expression.Error: We cannot convert the value “” to type Table.
Details:
Value=
Type=[Type]
In the report, I was trying to expand a Person or Group column, but not all of the field values were populated.
Here is the fix:
let
Source = SharePoint.Tables("https://taco.sharepoint.com/sites/food", [Implementation="2.0", ViewMode="All"]),
#"abcc-b8fe-4b23-be01-abc5f2c3320c" = Source{[Id="abcc-b8fe-4b23-be01-abc5f2c3320c"]}[Items],
#"Expanded Assigned User2" = Table.TransformColumns(#"abcc-b8fe-4b23-be01-abc5f2c3320c", {{"Assigned User", each if Value.Is(_,type list) then _{0} else [title = "not assigned"], type record}} ),
#"Expanded Assigned Analyst" = Table.ExpandRecordColumn(#"Expanded Assigned User2", "Assigned User", {"title"}, {"Assigned User.title"})
in
#"Expanded Assigned User"