Hi,

Is there a wild card merge I can use for a Column in power query?  i.e. I want to merge all columns with a title that starts Column*.  Or can this be achieved with some kind of loop?

Sorry for the noddy question.

Cheers

Alex

Hi Alex,

this is one posssible way:

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
    AddedIndex = Table.AddIndexColumn(Source, "Index", 0, 1),
    UnpivotOther = Table.UnpivotOtherColumns(AddedIndex, {"Index"}, "Attribute", "Value"),
    FilterStartWith = Table.SelectRows(UnpivotOther, each Text.StartsWith([Attribute], "Column")),
    RemoveCols = Table.RemoveColumns(FilterStartWith,{"Attribute"}),
    ChangeToText = Table.TransformColumnTypes(RemoveCols,{{"Value", type text}}),
    GroupByIndexAndCreateListOfColEntries = Table.Group(ChangeToText, {"Index"}, {{"test", each _[Value], type table}}),
    ExpandByTransformingListToText = Table.TransformColumns(GroupByIndexAndCreateListOfColEntries, {"test", each Text.TrimEnd(Lines.ToText(_,"-"),"-")}),
    MergeResultWithSourceTable = Table.NestedJoin(AddedIndex,{"Index"},ExpandByTransformingListToText,{"Index"},"NewColumn"),
    Expand = Table.ExpandTableColumn(MergeResultWithSourceTable, "NewColumn", {"test"}, {"test"}),
    RemoveIndexCol = Table.RemoveColumns(Expand,{"Index"}),
    ListOfUniqueMergedColumnNames = Table.Distinct(FilterStartWith, {"Attribute"})[Attribute],
    RemoveMergedCols = Table.RemoveColumns(RemoveIndexCol,ListOfUniqueMergedColumnNames)
in
    RemoveMergedCols

enjoy :-)