Merging dynamically each 2 columns Excel Power Query

[hiken]

Hello,
I've struggling for days with this and can't get it right, please assist.
I want to merge dynamically earch 2 columns in the table, the number of columns are changing and that's why

this is the column

and here is it after tranposing the table

and this is the expected output, each 2 columns merged with comma delimited

appreciate any help
Thanks.

[ImkeF · Accepted solution]

Hi @hiken ,
I would do it like so:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lFyNAIRxiDCRClWJ1rJCSTqBBJ1Aok6QUSdQaLOIFFnkKgzUDQWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    AddFieldValues = Table.AddColumn(#"Changed Type", "FieldValues", each Record.FieldValues(_)),
    AddPairs = Table.AddColumn(AddFieldValues, "Pairs", each {0..(List.Count([FieldValues])/2)-1}),
    AddCombinations = Table.AddColumn(AddPairs, "Combinations", each List.Transform([Pairs], (l)=> Text.Combine(List.Range([FieldValues],(l*2),2), ", "))),
    CreateNewTable = Table.FromRows( AddCombinations[Combinations] )
in
    CreateNewTable

Please also check the file enclosed.