Forum Discussion
Show Table Columns as Row
Hi All,
Seeking you help on my issue. I have a this table below which has a column that I need to convert as rows
| Branch | Year | Month | Total Product | Total Sales | House Loan | Car Loan | Preferred Client |
| Asia | 2019 | 12 | 5 | 100,000.00 | Yes | Yes | |
| Asia | 2020 | 1 | 1 | 500,000.00 | Yes | Yes | |
| Asia | 2020 | 2 | 6 | 2,010,150.00 | Yes | Yes | Yes |
| Asia | 2021 | 1 | 15 | 30,000.00 | Yes | Yes | |
| Asia | 2021 | 2 | 18 | 35,120.00 | |||
| Europe | 2020 | 12 | 50 | 101,010.00 | Yes | ||
| Europe | 2020 | 11 | 100 | 202,020.00 | Yes | Yes | |
| Europe | 2021 | 2 | 25 | 5,000.00 | |||
| Europe | 2021 | 3 | 20 | 1,000.00 | Yes | ||
| Europe | 2021 | 4 | 30 | 25,000.00 | Yes |
I need the last 3 columns to be shown as row as shown below (summarize per branch, year and month column)
| Branch | Year | Month | Key Drivers | Total Product | Total Sales |
| Asia | 2019 | 12 | House Loan | 5 | 100000 |
| Asia | 2019 | 12 | Car Loan | ||
| Asia | 2019 | 12 | Preferred Client | 5 | 100000 |
| Asia | 2020 | 1 | House Loan | ||
| Asia | 2020 | 1 | Car Loan | 1 | 500,000.00 |
| Asia | 2020 | 1 | Preferred Client | 1 | 500,000.00 |
| Asia | 2020 | 2 | House Loan | 6 | 2,010,150.00 |
| Asia | 2020 | 2 | Car Loan | 6 | 2,010,150.00 |
| Asia | 2020 | 2 | Preferred Client | 6 | 2,010,150.00 |
Any solution is highly appreciated.
Looking forward for you advise. Thank you!
Hi Anonymous ,
Just follow the formula in the article and create the following calculation table
Table1 = FILTER( UNION( SELECTCOLUMNS('Table',"Branch",[Branch],"Year",[Year],"Month",[Month],"Total Product",[Total Product],"Total Sales",[Total Sales],"Key Drivers",[Column]), SELECTCOLUMNS('Table',"Branch",[Branch],"Year",[Year],"Month",[Month],"Total Product",[Total Product],"Total Sales",[Total Sales],"Key Drivers",[Column 2]), SELECTCOLUMNS('Table',"Branch",[Branch],"Year",[Year],"Month",[Month],"Total Product",[Total Product],"Total Sales",[Total Sales],"Key Drivers",[Column 3]) ), [Key Drivers]<>"")Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Anonymous , Try unpivot in power query
Hi Anonymous ,
Please kindly refer to
You only need one more step than the steps in the article and replace YES with the corresponding column name.
You can create the calculated column like
Column = IF([House Loan]="Yes","House Loan")Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
Just follow the formula in the article and create the following calculation table
Table1 = FILTER( UNION( SELECTCOLUMNS('Table',"Branch",[Branch],"Year",[Year],"Month",[Month],"Total Product",[Total Product],"Total Sales",[Total Sales],"Key Drivers",[Column]), SELECTCOLUMNS('Table',"Branch",[Branch],"Year",[Year],"Month",[Month],"Total Product",[Total Product],"Total Sales",[Total Sales],"Key Drivers",[Column 2]), SELECTCOLUMNS('Table',"Branch",[Branch],"Year",[Year],"Month",[Month],"Total Product",[Total Product],"Total Sales",[Total Sales],"Key Drivers",[Column 3]) ), [Key Drivers]<>"")Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
