Forum Discussion
List and Table Extracting employee name using power query
Hi all,
Iam extracting data using GET DATA from excel connection to a sharepoint list online, now while i have already extracted and in power query editor i have a column were the records are coming from address book which are employees name. some line have multiple names/ currently it is showing as List and Table but i want to extract employee which is not working using left and right arrow.
please help!
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.
15 Replies
aqeel_shaikh Hi! can you paste your M code of the advanced editor of the power query?
BBF
💡 Did I answer your question? Mark my post as a solution!
👍 Kudos are appreciated
🔥 Proud to be a Super User!
Hi aqeel_shaikh,
Thank you for reaching out to Microsoft Fabric Community Forum!
It looks like you're working with a column in Power Query that contains either List or Table values, commonly seen when pulling data from an address book or SharePoint-connected Excel file. To help you extract employee names from such data structures, here's how you can proceed.
In the Power Query Editor, start by locating the column that contains the List or Table values. You’ll notice a small icon next to each cell indicating the data type. This will help you identify the column that needs expansion.
Next, click the expand icon located at the top-right of the column header. If the column contains a List, Power BI will offer to convert the list into multiple rows. If it contains a Table, Power BI will display a list of fields available within the table, commonly including fields like DisplayName or Email. From there, you can select the DisplayName field to extract employee names.
If a single cell contains multiple employee names stored as a list, Power Query will break them into multiple rows during expansion. This allows each employee name to appear in its own row, making the data easier to work with and analyze further based on your report needs.
If you prefer to keep all employee names in a single row as a comma-separated string, you can use the Group By feature in the Transform tab. Group the data by a unique identifier such as a Record ID, and create a custom column using the Text.Combine function to merge the names into one field.
Once you're done transforming and formatting the names as needed, click on Close & Apply to load the cleaned-up data into your Power BI model. This will ensure the employee names are correctly displayed and ready for use in reports or dashboards.
We have shared the official documents below for your guidance:
Perform common query tasks in Power BI Desktop - Power BI | Microsoft Learn
Create a report on a SharePoint List in Power BI Desktop - Power BI | Microsoft Learn
Create a semantic model from a SharePoint List - Power BI | Microsoft Learn
Thank you.Hi aqeel_shaikh,
Hi,
I wanted to check in your situation regarding the issue. Have you resolved it? Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!
Hi aqeel_shaikh,
Just wanted to follow up and confirm that everything has been going well on this. Please let me know if there’s anything from our end.
Please feel free to reach out Microsoft fabric community forum.
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
NameHi 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?
