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,
From what I understand, after you recieve your records, I created a dummy table to replicate. Image 1 is the Source and Image 2 is the output. I'll leave the code below for that. Let me know if I have understood your query correctly. If not, please provide the source and the ouput you'd like.
let
Source = #table(
type table [EmployeeNames=any],
{
{"John Smith"},
{{"Mary Jones", "David Clark"}},
{#table(type table [Name=text], {{"Alex Ray"}})},
{{"Priya Nair", "Chen Lee", "Rahul Mehta"}},
{null}
}
),
List = Table.TransformColumns ( Source , { "EmployeeNames" , each try List.Combine ( Table.ToColumns ( _ ) ) otherwise _ } ),
Name = Table.TransformColumns ( List , {"EmployeeNames" , each try Text.Combine ( _ , ", " ) otherwise _ } )
in
Name
Hi SundarRaj , below is the M code from advance editor in power query. i want to transform column name "Reportedby" which is in table to veiw employees name. would you please help me rewrite code based on my data. thanls
let Source = SharePoint.Tables("table", [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"}) in #"Reordered Columns1"Hi aqeel_shaikh, thanks for this. Could you share the dataset that you are using for this purpose?
Hi SundarRaj , the data set is confidential so i will not be able to share this, but i can share a screenshot,
Also, when i try to click on the arrow next to column name it doesn't show me any records. Note that the reportedby is employee name which is coming from address book.
