How to combine or group multiple column into a new table

[sophtee]

Hello everyone,

I would like to combine or group multiple columns of related row and place into a new table.

For example:I would like to have another table with HchID , Flk (Flk1 & Flk2) and respective Qty. Please note that I am using this data for other reports.

So I would like the new table to be T2:

[wstegemann · Accepted solution]

Hi @sophtee,

If you have the source data available in power query, you can do the following:

1. Duplicate the source table in power query (do this twice if you would like to keep a copy of the source table)
2. Remove the flk2 and qty2 columns from the original table
3. Remove the flk1 and qty1 columns from the duplicated table
4. Rename the remaining flk and qty columns so that the columns on both tables are named the same
5. Append the original table and the duplicated table
6. Optionally filter to remove blanks

[v-yohua-msft · Accepted solution]

Hi, @sophtee 

Based on your information, I create a table:

You can use DAX to create what you want. Create two new tables, get data for 1 and 2, and combine them together

Table1 = SELECTCOLUMNS('Table', "HchID", 'Table'[HchID], "Flk", 'Table'[Flk1], "Qty", 'Table'[Qty1])

Table2 = SELECTCOLUMNS('Table', "HchID", 'Table'[HchID], "Flk", 'Table'[Flk2], "Qty", 'Table'[Qty2])

FinalTable = UNION(Table1, Table2)

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wstegemann · Accepted solution]

Hi @sophtee,

If you have the source data available in power query, you can do the following:

1. Duplicate the source table in power query (do this twice if you would like to keep a copy of the source table)
2. Remove the flk2 and qty2 columns from the original table
3. Remove the flk1 and qty1 columns from the duplicated table
4. Rename the remaining flk and qty columns so that the columns on both tables are named the same
5. Append the original table and the duplicated table
6. Optionally filter to remove blanks