Merge columns dynamically

[shift-del]

Hello

I want to select columns in the worksheet and then merge them in PQ. The first part is done. The second part drives me crazy.
I searched the web and the best I could find was this:
Stackoverflow.com
But it errors with:

Expression.Error: Das Feld "NAME" des Datensatzes wurde nicht gefunden.

I tried Copliot but that got me in a circle of errors.

My file on OneDrive:

Dynamisches Verketten von Spalten in PQ.xlsx

1drv.ms

qry_Kundenstammdaten:Debitoren
Is the standard query for merging columns. That is how it should look in the end.

The numbered queries are the combined attempts of me and Copilot.

 

[smozgur]

If all columns will be merged except the last column (or last N columns) then I would create a list of column names by using the Table.ColumnNames function, and drop the last (or last N) list item by using the List.RemoveLastN function, and finally, use this list as the sourceColumns list parameter of the Table.CombineColumns function as shown below.

let
    Source = Tabelle1,
    ColumnsToBeMerged = List.RemoveLastN(Table.ColumnNames(Source), 1),
    MergedColumns = Table.CombineColumns(Source,ColumnsToBeMerged,Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Kunde")
in
    MergedColumns

 

[shift-del]

Smozgur

Sorry, that is not what I want/need.
As you can see in my file there is a dropdown in G2 where you can choose which combination you want. So, it is mandatory to use this.

 

[smozgur]

My bad.

How about the following? Getting the column names from your function query: Variante_Anzeige_Kundenname_fnViaQuery.

let
    Source = Tabelle1,
    _separate = Text.Split(Variante_Anzeige_Kundenname_fnViaQuery,", "),
    MergedColumns = Table.CombineColumns(Source,_separate,Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Kunde")
in
    MergedColumns

Or do you want to drop the other columns which are not in the mandatory selection?

There are so many failing queries in the sample model. So, instead of spending time with those codes and patch them by using another source or method, I prefer to know the rules and try to create the final query in my own way.

 

[shift-del]

Thanks Smozgur, that's it.

Removing the unnecessary columns is simple again:

Table.SelectColumns(MergedColumns,{"Kunde", "Kundennummer"})

 

[smozgur]

Modified version of your query (3), in case you would like to continue from this point.

let
    Source = Tabelle1,
    _separate = Text.Split(Variante_Anzeige_Kundenname_fnViaQuery,", "),
    #"Added Custom" = Table.AddColumn(Source, "Benutzerdefiniert", each Text.Combine(Record.ToList(Record.SelectFields(_, _separate)), " - "))
in
    #"Added Custom"

 

[smozgur]

Thanks Smozgur, that's it.

Removing the unnecessary columns is simple again:

Table.SelectColumns(MergedColumns,{"Kunde", "Kundennummer"})

Correct.

Glad to hear it works. Thanks for the feedback.