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 2...
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!
PaulDBrown
Community Champion
Community ChampionUsing 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)