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.