Person/ People Field in SharePoint List - No Split available

[Ejules]

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

[V-pazhen-msft]

@Ejules 
Glad you solved yourself, and thanks for sharing this non-list value issue and the resolution.😋

Paul Zheng _ Community Support Team

[Ejules · Accepted solution]

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.