Show Table Columns as Row

[Anonymous]

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!

[Anonymous]

Hi @Anonymous ,

Please kindly refer to 

DAX Unpivot

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.

[Anonymous]

Thank you for your response and to the link to the similar query before. I need to deep dive into how I can manage those with multiple values into one column.(House Loan = YES; Preferred Client = YES)

[Anonymous · Accepted solution]

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.

[Anonymous]

thank you Stephen!. I followed the instruction as stated. The solution works like a charm 🙂

[amitchandak]

@Anonymous , Try unpivot in power query

https://radacad.com/pivot-and-unpivot-with-power-bi

[Anonymous]

thank you for your response, unfortunately the 3 columns are not available (visible) in the power query editor.