List and Table Extracting employee name using power query

[aqeel_shaikh]

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!

[SundarRaj]

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

[aqeel_shaikh]

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"

[SundarRaj]

Hi @aqeel_shaikh, thanks for this. Could you share the dataset that you are using for this purpose?

[aqeel_shaikh]

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.

[v-sgandrathi]

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.

[aqeel_shaikh]

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"

[v-sgandrathi]

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.

[aqeel_shaikh]

@v-sgandrathi , Hi since the data is sensitive i can share below screenshot

[v-sgandrathi]

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.

[v-sgandrathi]

Hi @aqeel_shaikh,

Hi  

We wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.

If you need any further assistance, feel free to reach out.

Thank you for being a valued member of the Microsoft Fabric Community Forum!

[v-sgandrathi]

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.

[v-sgandrathi]

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.

[v-sgandrathi]

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!

[v-sgandrathi]

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.

[BeaBF]

@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!