Merging the 100 columns

Alirezam To automate the task you can unpivot all columns and then combine all values in a single cell. Here is a code example for 5 columns which will work for 100 as well. You can copy syntax starting #"Unpivoted Columns".

This code unpivots all columns and the first-row value will be stored in "Value" column. Then we group by and convert into text list. And at last, remove unnecessary columns.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmMgNgFiU6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [a = _t, b = _t, c = _t, d = _t, e = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"a", Int64.Type}, {"b", Int64.Type}, {"c", Int64.Type}, {"d", Int64.Type}, {"e", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {}, "Attribute", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Columns",{{"Value", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {}, {{"Combine", each _, type table [Attribute=text, Value=nullable text]}}),
    #"TransformColumnToText" = Table.AddColumn(#"Grouped Rows", "List", each Text.Combine(Table.ToList([Combine]), ", ")),
    #"Removed Columns1" = Table.RemoveColumns(TransformColumnToText,{"Combine"})
in
    #"Removed Columns1"

Hi Alirezam ,

You can also try this:

1. Transpose your table.

2. Add a custom column.

3. Group.

4. Change the formula.

5. Remove the custom column.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

See if the following works:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTICYmMgNgFiUyA2A2JzILYAYksgNjRQio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t]),
    #"Merged Columns" = 
        Table.CombineColumns(
            Source,
            Table.ColumnNames(Source),
            Combiner.CombineTextByDelimiter(":", QuoteStyle.None),
            "Merged"
        )
in
    #"Merged Columns"

Normally the second parameter is the columns you select and would be {"Column1","Column2",etc}
I replaced that with Table.ColumnNames(TableName) which is a list of all columns in the table.

It turns this:

into this:

I only had 10 columns, and I used a colon to separate the merged values. You can switch that. This will work with any number of columns.

If that isn't your goal Alirezam can you provide some sample data (3-4 columns is enough) and expected output?