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 v-sgandrathi , as advised i have edited the M code, but it is showing error (Expression.SyntaxError: Token ',' expected). Can you please check if anything is wrong in the M code shared below.
Thanks
let
Source = SharePoint.Tables("https://emiratesgroup.sharepoint.com/sites/CMU882/", [Implementation=null, ApiVersion=15]),
#"4f8ffbf1-096a-40d3-bede-6aa20b811c5c" = Source{[Id="4f8ffbf1-096a-40d3-bede-6aa20b811c5c"]}[Items],
#"Changed Type" = Table.TransformColumnTypes(#"4f8ffbf1-096a-40d3-bede-6aa20b811c5c",{{"EndDate", type date}, {"EffectiveDate", type date}, {"ReportDate", type date}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"FileSystemObjectType", "Id", "ServerRedirectedEmbedUri", "ServerRedirectedEmbedUrl", "ContentTypeId"}),
#"Expanded Supplier" = Table.ExpandRecordColumn(#"Removed Columns", "Supplier", {"Parent_Company"}, {"Supplier.Parent_Company"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Supplier",{"Title", "Supplier.Parent_Company", "OData__ColorTag", "ComplianceAssetId", "ReportDate", "BenefitsType", "BenefitsDescription", "ReportedbyId", "ReportedbyStringId", "ValueCreated", "Value(AED)", "EffectiveDate", "EndDate", "Attachment", "Reference", "Mode", "SupplierId", "ReportingStatus", "CMUAppLinkId", "CMUReceivablesStatus", "Created", "Tasking", "ID", "Modified", "AuthorId", "EditorId", "OData__UIVersionString", "Attachments", "GUID", "FirstUniqueAncestorSecurableObject", "RoleAssignments", "AttachmentFiles", "ContentType", "GetDlpPolicyTip", "FieldValuesAsHtml", "FieldValuesAsText", "FieldValuesForEdit", "File", "Folder", "LikedByInformation", "ParentList", "Properties", "Versions", "Reportedby", "CMUAppLink", "Author", "Editor"}),
#"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"OData__ColorTag", "ComplianceAssetId"}),
#"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns1",{"Title", "Supplier.Parent_Company", "BenefitsType", "ReportDate", "BenefitsDescription", "ReportedbyId", "ReportedbyStringId", "Reportedby", "ValueCreated", "Value(AED)", "EffectiveDate", "EndDate", "Attachment", "Reference", "Mode", "SupplierId", "ReportingStatus", "CMUAppLinkId", "CMUReceivablesStatus", "Created", "Tasking", "ID", "Modified", "AuthorId", "EditorId", "OData__UIVersionString", "Attachments", "GUID", "FirstUniqueAncestorSecurableObject", "RoleAssignments", "AttachmentFiles", "ContentType", "GetDlpPolicyTip", "FieldValuesAsHtml", "FieldValuesAsText", "FieldValuesForEdit", "File", "Folder", "LikedByInformation", "ParentList", "Properties", "Versions", "CMUAppLink", "Author", "Editor"}),
#"ExpandTables" = Table.TransformColumns(SharePoint.Tables, {"Reportedby", 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"
Hi aqeel_shaikh,
Thank you for sharing the M code. I noticed a syntax issue at the final step (#"ExpandTables"), which could be related to the custom transformation logic.
To help troubleshoot, could you provide the PBIX file or a sample version, as long as it doesn't contain any sensitive or confidential information?
This will allow us to review the steps and offer a more accurate solution.
Thank you.
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.