Search code examples
powerbipowerquerypowerbi-desktopmsharepoint-list

How do I collapse an expanded column into single rows?

I have a SharePoint list containing a column with data type People or Group which can contain multiple people. When this list is imported into Power BI it appears as [Table] in the Power BI Query Editor.

enter image description here

When I expand this column (using highlighted button above), it creates multiple rows (which I don't want).

My goal is to preserve the row count of my table by converting all duplicate rows created by the expansion back to single rows with a delimiter between values. Has anyone found a way to consolidate this?

Data example

Original Data

ID ColumnHeader OtherColumns
1 [Table] OtherData

After expansion

ID ColumnHeader OtherColumns
1 [email protected] OtherData
1 [email protected] OtherData

Target output

ID ColumnHeader OtherColumns
1 [email protected]# [email protected] OtherData

*The delimiter can be anything (not neccesarily a #)

Solution

  • Assume you have a table like this.

    enter image description here

    Table (in green) contains data structured like this.

    enter image description here

    To achieve the concatenation you're after as follows:

    enter image description here

    Add a custom column with the following code.

    Text.Combine([ColumnHeader][Column Header A],"# ")