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.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.