Merge columns dynamically

shift-del

shift-del

Well-known Member
Joined
Aug 28, 2009
Messages
820
Office Version
  1. 365
Platform
  1. Windows
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:
Power Query: 
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 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.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
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.

Power Query: 
let
    Source = Tabelle1,
    ColumnsToBeMerged = List.RemoveLastN(Table.ColumnNames(Source), 1),
    MergedColumns = Table.CombineColumns(Source,ColumnsToBeMerged,Combiner.CombineTextByDelimiter(" - ", QuoteStyle.None),"Kunde")
in
    MergedColumns
 
Upvote 0
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.
 
Upvote 0
My bad.

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

Power Query: 
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.
 
Upvote 0
Solution
Thanks Smozgur, that's it.

Removing the unnecessary columns is simple again:
Power Query: 
Table.SelectColumns(MergedColumns,{"Kunde", "Kundennummer"})
 
Upvote 0
Modified version of your query (3), in case you would like to continue from this point.

Power Query: 
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"
 
Upvote 0
shift-del said:
Thanks Smozgur, that's it.

Removing the unnecessary columns is simple again:
Power Query: 
Table.SelectColumns(MergedColumns,{"Kunde", "Kundennummer"})
Click to expand...
(y) Correct.

Glad to hear it works. Thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

jlhoover3
  • Solved
Power Query - Filter table column based on cell value
Replies
3
Views
201
alansidman
A
B
  • Solved
Equivalent of Text to Columns in Power Query
Replies
4
Views
414
borkybork
B
B
  • Question
Power Query - Expression.Error Column wasn't found
Replies
2
Views
470
Automatrix
A
Q
  • Solved
Split Column by Delimiter and add new column Name Dynamically
Replies
2
Views
354
QMAN223
Q
E
  • Question
Dynamic Sheet Name - Power Query
2 3
Replies
23
Views
5K
egoosen3
E

Forum statistics

Threads
1,216,499
Messages
6,131,012
Members
449,613
Latest member
MedDash99

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top