Pivot or Transpose Table using DAX rather than Power Query

[campbellmurphy]

Hi Everyone,

I have Table 1 (below) which was created using the DAX GENERATE function.  I now need to pivot this table to adjust the columns (Table 2).  Normally I'd perform this operation in Power Query using the Pivot function however I don't believe it's possible to make Power Query adjustments on a table if it's been generated in DAX (if so please provide more information).  

I understand from the forum posts below that there is a solution using Summarize and potentially Union however for the life of me I'm not able to figure it out. Any assistance is greatly appreciated.

Table 1: Raw Data (roughly 100k rows, 10 columns in actual dataset)

Index	Amount	Date	Location
1	500	1/1/2020	Australia
2	200	1/2/2020	Canada
3	2000	1/3/2020	UK
2	2000	1/4/2020	Australia
3	4500	1/5/2020	USA
1	100	1/6/2020	Canada
1	300	1/4/2020	UK
1	5000	1/6/2020	China
2	4000	1/6/2020	Australia
2	200	1/2/2020	UK
2	3000	1/1/2020	China
3	2000	1/1/2020	Australia
1	300	1/1/2020	USA
1	5000	1/2/2020	Canada
1	4000	1/3/2020	UK
1	300	1/4/2020	Australia
1	5000	1/5/2020	USA
1	4000	1/6/2020	Canada
1	200	1/4/2020	UK
1	3000	1/6/2020	China
1	2000	1/6/2020	Australia
1	2000	1/2/2020	USA
2	300	1/1/2020	Australia
2	5000	1/1/2020	Canada
2	4000	1/1/2020	UK
2	300	1/2/2020	Australia
2	5000	1/3/2020	USA
2	2000	1/4/2020	Canada
2	300	1/5/2020	UK
2	5000	1/6/2020	China
2	4000	1/4/2020	Australia
2	300	1/1/2020	UK
3	5000	1/2/2020	China
3	4000	1/3/2020	Australia
3	2000	1/4/2020	USA
3	300	1/5/2020	Canada
3	5000	1/6/2020	UK
3	4000	1/4/2020	Australia
3	300	1/6/2020	USA
3	5000	1/6/2020	Canada
3	4000	1/2/2020	UK
3	200	1/1/2020	China
3	3000	1/1/2020	Australia

Table 2: Transformed Data(roughly 50 rows, 2k columns. Columns will likely be filtered down to 200-300)

Date	Location	1	2	3
1/01/2020	Australia	500	300	5000
1/01/2020	Canada	null	5000	null
1/01/2020	China	null	3000	200
1/01/2020	UK	null	4300	null
1/01/2020	USA	300	null	null
1/02/2020	Australia	null	300	null
1/02/2020	Canada	5000	200	null
1/02/2020	China	null	null	5000
1/02/2020	UK	null	200	4000
1/02/2020	USA	2000	null	null
1/03/2020	Australia	null	null	4000
1/03/2020	UK	4000	null	2000
1/03/2020	USA	null	5000	null
1/04/2020	Australia	300	6000	4000
1/04/2020	Canada	null	2000	null
1/04/2020	UK	500	null	null
1/04/2020	USA	null	null	2000
1/05/2020	Canada	null	null	300
1/05/2020	UK	null	300	null
1/05/2020	USA	5000	null	4500
1/06/2020	Australia	2000	4000	null
1/06/2020	Canada	4100	null	5000
1/06/2020	China	8000	5000	null
1/06/2020	UK	null	null	5000
1/06/2020	USA	null	null	300

Example visualisations to be created:

Example Power BI file:

https://drive.google.com/file/d/1i445hX9IAD5PoTWmVrAn9BDVl_dj7mSg/view?usp=sharing 

Similiar issues that have happened to others

• https://community.powerbi.com/t5/DAX-Commands-and-Tips/Transpose-table/m-p/719213 
• https://community.powerbi.com/t5/Desktop/How-to-summarize-and-Transpose-headers-for-rows/m-p/532904 

Cheers,

Campbell

[mahoneypat]

In general, you should not pivot your data like this.  Please describe/show your desired final visual that you believe requires this format, so that an alternate approach can potentially be suggested.

Pat

[campbellmurphy]

Hey @mahoneypat,

Thanks for the feedback.  I've updated my original post and the associated example power bi file. The most obvious alternative solution I can think of is to use ADDCOLUMNS when creating a new table within DAX with each new column being filtered for a particular index. This doesn't scale well over 1000 columns which are likely to change into the future though. Your feedback is greatly appreciated.

Cheers,

Campbell 

[MFelix · Accepted solution]

Hi @campbellmurphy 

Looking at your updated information you don't need to pivot the information, if you use the Index column on the legend of the visualizatiions and change the X-axis according to your needs you can do it has you need, further more yoiu can create an hierarchy with the index and location.

Or do you have some other needs in terms of visualization that is not explained in the example you have?

I used a slicer to filter information by index.

[MFelix]

Hi @campbellmurphy ,

Do you just want to present the information has you have it in the last image? or can it be a little bit different?

If it can have a slitgh  look and fell use a matrix visualization something similar to this:

If you want the exaxt look and fell you can try and do what is in this blog post:

https://businessintelligist.com/2020/07/27/power-bi-dax-tutorial-how-to-pivot-data-using-dax-using-r...

[campbellmurphy]

Hi @MFelix ,

I want the data transformed as it is in the image e.g. in a new table (not a matrix) as I need to use each of the new index column numbers as unique variables in visualisations. 

The blog post provided manually creates each column which I'd strongly prefer not to do as I'd have 2000 columns which may change data sources over time.

Thanks,

Campbell