DAX Unpivot

[JamesT23]

Hi 

I have a set of data i need to unpivot in two ways and don't seem to be able to do it. Can someone help me. I do this in SQL currently using AS and UNION but need to tansfer to power query.

Company	Date	Vendor 1	Vendor 2	Vendor 3	Charge Type 1	Charge Type 2	Charge Type 3	Charge 1	Charge 2	Charge 3
Dell	2018	HP	Epson	Cisco	Printers	Camera	Switch	100	200	150
Dell	2019	Microsoft	Epson	HP	Licenses	Camera	Server	150	200	350

This is how i need one result to look 

Company	Date	Vendor	Charge Type	Charge
Dell	2018	HP	Printers	100
Dell	2018	Epson	Camera	200
Dell	2018	Cisco	Switch	150
Dell	2019	Microsoft	Licenses	150
Dell	2019	Epson	Camera	200
Dell	2019	HP	Server	350

This is how i need the send to look where i concat Vendor and charge types. 

Company	Date	Vendor	Charge
Dell	2018	HP-Printers	100
Dell	2018	Epson-Camera	200
Dell	2018	Cisco-Switch	150
Dell	2019	Microsoft-Licenses	150
Dell	2019	Epson-Camera	200
Dell	2019	HP-Server	350

Please also be aware in your solution that sometimes I need to join Vendor 1 with Charge Type 2

[v-xicai · Accepted solution]

Hi @JamesT23 ,

You can create new Table2.

Table2 =

FILTER(

UNION(

SELECTCOLUMNS('Table1',"Company",[Company],"Date",[Date],"Vendor",[Vendor 1],"Charge Type",[Charge Type 1],"Charge",[Charge 1]),

SELECTCOLUMNS('Table1',"Company",[Company],"Date",[Date],"Vendor",[Vendor 2],"Charge Type",[Charge Type 2],"Charge",[Charge 2]),

SELECTCOLUMNS('Table1',"Company",[Company],"Date",[Date],"Vendor",[Vendor 3],"Charge Type",[Charge Type 3],"Charge",[Charge 3])

),

"Vendor"<>""&&"Charge Type"<>""&&"Charge"<>"")

 

Then, create column Vendor_Concatenate in Table2.

Vendor_Concatenate = CONCATENATE(Table2[Vendor],Table2[Charge Type])

 

Here is my test pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EdlYsKW-CSJLjxmFNN...

Best Regards,

Amy

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

[JamesT23]

Thanks Amy!