Forum Discussion
Combine multiple columns to a single list using Power Query
I was able to get the desired results through the UI.
- Merged the table with itself (inner join) by matching Code_1
- Expanded the table with column Name_1
- Repeated the process for all Code/Name columns
- Merged multiple Name columns to get a single column
This is a very cumbersome process and was hoping there's a smarter code that can do this.
Thanks for the advice!
Community ChampionAnonymous I like your solution PaulDBrown. Just chiming in here with a Power Query solution. See attached PBIX file beneath signature.
Thank you PaulDBrown and Greg_Deckler for the swift response!
Greg_Deckler, I was hoping to find a way without having to create multiple tables. I have a lot of columns in my actual dataset and don't want these redundant tables sitting around.
Also, I realize that my ultimate goal might not work with this solution. I wanted to use the new table as a lookup for the original table. Eg creating a new Column (Name_X) in the Original table that has the name of the code based on the rest of the columns -
Code_1(Data) Name_1(Data) Code_2(Data) Name_2(Data) Code_X(Data) Name_X(Final Result) 10 Name A 10.1 Name E 10 Name A 10 Name B 10.2 Name F 10.2 Name F 20 Name C 20.1 Name G 20.1 Name G - Greg_Deckler
Anonymous Oh, that was just an example, you could do all of that in a single query. Just have to copy and paste the right parts of the code.
Greg_Deckler , I was able to make it work, thanks!
Can you also help me on my expanded request? It's like doing a vlookup on a range. I want to find the Name of the Code from anywhere within the same table.
Having created a new table, I cannot really reference the values back to the original table.