I have the function which merges all columns as shown.

M Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    MergeAllColumns = Table.AddColumn(Source, "Merged", each 
            let 
            CreateList = Record.ToList(_),
            ConvertToText = List.Transform(CreateList, each Text.From(_)), 
            Concatenate = Text.Combine( ConvertToText, " ")
            in 

            Concatenate
    
     ),
    #"Removed Other Columns" = Table.SelectColumns(MergeAllColumns,{"Merged"})
in
    #"Removed Other Columns"

I wish to modify this code so that, I can say essentially say "Merge Other Cells". In this case, it's just column one which I can leave alone but ideally, I would like to be able to state which columns should remain unmerged and then everything not included in that will be dynamically merged no matter how many columns there are. Hope that makes sense, thanks.

CodePudding user response：

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
Exclude={"Column1", "Column3"},
List=List.Difference(Table.ColumnNames(Source),Exclude),
MergeAllColumns= Table.AddColumn(#"Added Index","Custom", each Text.Combine(Record.ToList( Table.SelectColumns(#"Added Index",List){[Index]}), " ")),
#"Removed Columns" = Table.RemoveColumns(MergeAllColumns, List.Combine({{"Index"}, List}))
in #"Removed Columns"