Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Cheers,
Campbell
Solved! Go to Solution.
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsIn 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
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.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |