Forum Discussion
Person/ People Field in SharePoint List - No Split available
Dear Community,
I'm fairly new to Power Bi, trying probably something quite basic š.
In the SharePoint List, there are several custom fields which are of the type people/person.
I crawled through Google and community, but neither of the mentioned solutions seems to work (most solutions either go via relationship & user information list or split the content).
My problem is it seems to be nested:
--> Whereas for the SharePoint default column the "Split Arrows" are available, as you can see, they are not for this column
--> Looking into it, is somehow is a column with a 'List' inside
--> each List Item seems to have a "Record inside"
And accessing this column, there would be the info:
I akwardly somehow got to extract it like this:
#"HinzugefĆ¼gte benutzerdefinierte Spalte2" = Table.AddColumn(#"Erweiterte GeƤndert von zu Email", "Benutzerdefiniert", each Table.ExpandListColumn(#"Erweiterte GeƤndert von zu Email", "Zugeteilt an")),
#"Erweiterte Benutzerdefiniert" = Table.ExpandTableColumn(#"HinzugefĆ¼gte benutzerdefinierte Spalte2", "Benutzerdefiniert", {"Zugeteilt an"}, {"Benutzerdefiniert.Zugeteilt an"}),
#"Erweiterte Benutzerdefiniert.Zugeteilt an" = Table.ExpandRecordColumn(#"Erweiterte Benutzerdefiniert", "Benutzerdefiniert.Zugeteilt an", {"email"}, {"Benutzerdefiniert.Zugeteilt an.email"}),
#"Umbenannte Spalten" = Table.RenameColumns(#"Erweiterte Benutzerdefiniert.Zugeteilt an",{{"Benutzerdefiniert.Zugeteilt an.email", "Zugetailt an: Email"}}),
--> However, if the field is empty, I get an error on that row.
Thanks for any help,
Kind regards,
Ejules
ok, I think i solved it, turns out SharePoint List Blank is neither Blank nor null, this causes the issue.
Workaround: Replace Non List Items with null, then it works as expected:1. Add a Step:
2. in Custom Editor, replace the Step with:
#"New Step" = Table.TransformColumns( #"PreviousStep", {{"YourColumn", each if Value.Is(_,type list) then _ else null}} ),
3. Normal Split is now available:
4.
5.
2 Replies
ok, I think i solved it, turns out SharePoint List Blank is neither Blank nor null, this causes the issue.
Workaround: Replace Non List Items with null, then it works as expected:1. Add a Step:
2. in Custom Editor, replace the Step with:
#"New Step" = Table.TransformColumns( #"PreviousStep", {{"YourColumn", each if Value.Is(_,type list) then _ else null}} ),
3. Normal Split is now available:
4.
5.
Ejules
Glad you solved yourself, and thanks for sharing this non-list value issue and the resolution.šPaul Zheng _ Community Support Team
