Forum Discussion
List and Table Extracting employee name using power query
Hi aqeel_shaikh,
Hello,
Thank you for reaching out to the Microsoft Fabric Community Forum.I have successfully reproduced your scenario where the Reportedby column contains a mixture of records, lists, and tables typically occurring when extracting Person/Group fields from a SharePoint list.
As you mentioned, expanding the column using the UI arrows doesn’t show any values this happens when Power Query detects inconsistent or complex structures. To address this, I used a custom column in Power Query that can intelligently detect and extract the employee names from each row, regardless of whether the value is a record, list, or table.
Output Achieved:
Here’s a summary of the final output based on your requirement:
- Single person: John Smith
- Multiple people: Jane Doe, Robert King
- From table: Alice Brown
- Nulls handled without error
- Displayed using Table, Matrix, and Card visuals
Here’s a preview of the result in visuals:
Hello,
Thank you for reaching out to the Microsoft Fabric Community Forum.I have successfully reproduced your scenario where the Reportedby column contains a mixture of records, lists, and tables typically occurring when extracting Person/Group fields from a SharePoint list.
As you mentioned, expanding the column using the UI arrows doesn’t show any values this happens when Power Query detects inconsistent or complex structures. To address this, I used a custom column in Power Query that can intelligently detect and extract the employee names from each row, regardless of whether the value is a record, list, or table.
Output Achieved:
Here’s a summary of the final output based on your requirement:
- Single person: John Smith
- Multiple people: Jane Doe, Robert King
- From table: Alice Brown
- Nulls handled without error
- Displayed using Table, Matrix, and Card visuals
Here’s a preview of the result in visuals:
What I Did:
In Power Query, I added a custom column with the following logic to extract Title from various formats:
if (Type.Is(Value.Type([Reportedby]), type record)) then
try [Reportedby][Title] otherwise null
else if (Type.Is(Value.Type([Reportedby]), type list)) then
Text.Combine(List.Transform([Reportedby], each try _[Title] otherwise null), ", ")
else if (Type.Is(Value.Type([Reportedby]), type table)) then
Text.Combine(List.Transform(Table.ToRecords([Reportedby]), each try _[Title] otherwise null), ", ")
else
null
For Your Reference, I’m attaching the .pbix file.
Thank you.
Hi aqeel_shaikh,
Thanks for explaining the issue clearly. I totally understand the confusion with the Reportedby column showing as List, Table, or blank when pulled from SharePoint this is common with employee fields coming from address book People/Person columns.
I faced something very similar, and the default expand arrows don't work because of how inconsistent the data structure is underneath.
Try using the following Power Query (M) transformation to extract and flatten the employee names properly.
let
// Your last step before this
SourceTable = #"Reordered Columns1",
// Clean up and extract employee names from Reportedby column
ExpandTables = Table.TransformColumns(SourceTable, {
"Reportedby", each try
if (Type.Is(Value.Type(_), type table)) then
Text.Combine(Record.ToList(_{0}), ", ")
else if (Type.Is(Value.Type(_), type list)) then
Text.Combine(List.Transform(_, each
if (Type.Is(Value.Type(_), type record)) then
Record.Field(_, "Title")
else
Text.From(_)
), ", ")
else
Text.From(_)
otherwise null
})
in
ExpandTables
This handles on single employee (stored as a table), multiple employees (stored as a list of records) and nulls or anything unexpected (gracefully)
Thank you.
Hi aqeel_shaikh,
As we did not get a response, may I know if the above reply could clarify your issue, or could you please help confirm if we may help you with anything else?
Your understanding and patience will be appreciated.