Forum Discussion
Extracting a SharePoint List Person Picker with empty values
Hi,
I'm currently working on creating a dashboard which uses a SharePoint Online List. The list uses a person picker for the "Assigned to" field.
What I want to do is extract the [title] field where multiple users exist and use a comma separator. I'm using the below M code for this. However it generates Errors where a user is not selected in the List. Is there a way to handle the blanks where these occur?
= Table.TransformColumns(#"...", {"Assigned to", each Text.Combine(List.Transform(Table.TransformRows(_, each [title]), Text.from), ", "), type text})
Sorry, I should say the error is
Expression.Error: We cannot convert the value null to type Table. So I gather that because the field is null that it can't perform the action of conversion?
I think I found a different solution.
I adapted this:
https://community.powerbi.com/t5/Desktop/Extract-table-from-column/td-p/1022979
To create a new column but used Table.SelectColumns to specify the exact column I wanted as so:
if [Assigned to] is table
then
Text.Combine(
List.Transform(
List.Combine(Table.ToRows(Table.SelectColumns([Assigned to], "title"])))
, each Text.From(_)
)
,","
)
else [Assigned to]
Which returns the items in the specified column delimited by comma, unless the original column value is null, in which case it returns a null.
1 Reply
I think I found a different solution.
I adapted this:
https://community.powerbi.com/t5/Desktop/Extract-table-from-column/td-p/1022979
To create a new column but used Table.SelectColumns to specify the exact column I wanted as so:
if [Assigned to] is table
then
Text.Combine(
List.Transform(
List.Combine(Table.ToRows(Table.SelectColumns([Assigned to], "title"])))
, each Text.From(_)
)
,","
)
else [Assigned to]
Which returns the items in the specified column delimited by comma, unless the original column value is null, in which case it returns a null.