Forum Discussion
Multiple People in single row from SharePoint list
I have a SharePoint list with a people column. When I expand the list in Power Query, I get a different row for each person. All the people in the column contribute to the project; therefore, I need to keep it together. My goal is to have the people from the original Sharepoint list in the same row separated by a semicolon.
Ex:
Sharepoint List Colum: Person A Person B
When expanded in PowerBI, would like to see Person A and Person B NOT two separate rows for each person
Thank you in advance
Hi, PBI8423
You can use this function to concatenate the values of a column into a single text string with a specified delimiter.
After you expand the SharePoint list in Power Query, select the column that contains the person's name. Go to the tab and click . In the window, select the drop-down list. Add a new step with the click of a button.
In the formula bar, type the following formula, and then press Enter:= Table.AddColumn(#"Grouped Rows", "People", each Text.Combine([Column1][People], "; "))Replace with the name of the previous step and replace with the name of the column. This will create a new column where the names are combined and separated by semicolons
You can check the following links:
Text.Combine - PowerQuery M | Microsoft Learn
Or use the "Group by" feature directly, and the related documentation is below
How to GROUP BY or summarize rows - Power Query | Microsoft Learn
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- Bipin-Lala
Solution Sage
Hi, PBI8423
You can use this function to concatenate the values of a column into a single text string with a specified delimiter.
After you expand the SharePoint list in Power Query, select the column that contains the person's name. Go to the tab and click . In the window, select the drop-down list. Add a new step with the click of a button.
In the formula bar, type the following formula, and then press Enter:= Table.AddColumn(#"Grouped Rows", "People", each Text.Combine([Column1][People], "; "))Replace with the name of the previous step and replace with the name of the column. This will create a new column where the names are combined and separated by semicolons
You can check the following links:
Text.Combine - PowerQuery M | Microsoft Learn
Or use the "Group by" feature directly, and the related documentation is below
How to GROUP BY or summarize rows - Power Query | Microsoft Learn
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
