Forum Discussion
Combine multiple columns to a single list using Power Query
Hello,
I am trying to create a new table that can be used for lookup and filtering.
Data
| Code_1 | Name_1 | Code_2 | Name_2 |
| 10 | Name A | 10.1 | Name C |
| 10 | Name A | 10.2 | Name D |
| 20 | Name B | 20.1 | Name E |
| 20 | Name B | 20.2 | Name F |
Result
| Code | Name |
| 10 | Name A |
| 20 | Name B |
| 10.1 | Name C |
| 10.2 | Name D |
| 20.1 | Name E |
| 20.2 | Name F |
Appreciate the assistance!
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!
6 Replies
Using DAX for a new table:
New =
VAR _T1 = SELECTCOLUMNS(Data, "Code", Data[Code_1], "Name", Data[Name_1])VAR _T2 = SELECTCOLUMNS(Data, "Code", Data[Code_2], "Name", Data[Name_2])
RETURN
UNION(_T1, _T2)
Anonymous 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 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.
