how to combine hundreds of text rows

[YannLG]

Hi, I have been looking for a solution all around, finding multiple ones but none that suits my needs.

I have an example of code that will combine rows of text (like the justify function in excel) by using the group function in power query.

The "key" is the first column "date", then followed by columns of text of which I combine rows respectively.

The end result gives a table with less rows but the same amount of columns with the text fo\rom each column text separated by ";".

input table:

date	column 3	column 4	column 5	colum 6
2021/7	A	E	A	E
2021/7	B	F	B	F
2022/8	C	G	C	G
2022/8	D	H	D	H

Output table

date	column 3	column 4	column 5	colum 6
2021/7	A,B	E,F	A,B	E,F
2022/8	C,D	G,H	C,D	G,H

"= Table.Group(#"Replaced Value", {"Date"}, {{"Column3",each Text.Combine([Column3],";")},{"Column4",each Text.Combine([Column4],"/n")},{"Column5",each Text.Combine([Column5],"/n")},{"Column6",each Text.Combine([Column6],"/n")}})."

Works great.

Now assume Thta I have hundreds of columns in the table (and not only 6 like in the example), how do I change the formula not to have to manually enter each column name?

something like

 " = Table.Group(#"Replaced Value", {"Date"}, Text.Combine(each column in the table),";")}

Thanks for your help!

[edhans · Accepted solution]

See this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtQ3V9JRcgRiVzgdq4Mk5QTEbnAaKmWkbwHkOgOxO5xGkXIBYg84HRsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, #"column 3" = _t, #"column 4" = _t, #"column 5" = _t, #"colum 6" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"date"}, "Attribute", "Value"),
    #"Grouped Rows" = 
        Table.Group(
            #"Unpivoted Other Columns", 
            {"date", "Attribute"}, 
            {
                {
                    "Combined Text",
                    each Text.Combine(_[Value], ", ")
                }
            }
        ),
    #"Pivoted Column" = Table.Pivot(#"Grouped Rows", List.Distinct(#"Grouped Rows"[Attribute]), "Attribute", "Combined Text")
in
    #"Pivoted Column"

It returns this:

What is did was:

• Select the Date and unpivoted other columns. This will let the logic work on an undefined number of columns for you.
• I then did a group by, and used the Text.Combine() function on the Values
• Then I Pivoted back the attribute column and told it not to aggregate the Values column.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[YannLG]

Thanks for you quick help, It worked nicely!