Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
The below is the original data:
User | Response 1 |
Response 2 | Response 3 | Response 4 | Response 5 |
ABC | Adopting | Performing | Adopting | Leading | Experienced |
DEF | Adopting | Leading | Performing | Adopting | Performing |
GHI | Performing | Experienced | Leading | Performing | Adopting |
I have added some calculated columns to the above table:
User | Response 1 | Response 2 | Response 3 | Response 4 | Response 5 | Critical Use 1 | Critical Use 2 | Critical USe 3 | Critical Use | Critical Use Calc | Creative 1 | Creative 2 | Creative_Calc | Creative |
ABC | Adopting | Performing | Adopting | Leading | Experienced | 40 | 30 | 40 | 36.67 | Adopting | 60 | 80 | 70 | Adopting |
DEF | Adopting | Leading | Performing | Adopting | Performing | 40 | 60 | 30 | 43.33 | Exploring | 40 | 30 | 35 | Performing |
GHI | Performing | Experienced | Leading | Performing | Adopting | 30 | 80 | 60 | 56.67 | Exploring | 30 | 80 | 55 | Adopting
|
These calculations are based on the below lookup tables:
Adopting | 40 |
Leading | 60 |
Performing | 30 |
Experienced | 80 |
Adopting | 36.67 |
Exploring | 43.37 |
Exploring | 56.67 |
Adopting | 55 |
Adopting | 70 |
Performing | 35 |
I now need to get the table to pivot the data as follows:
ABC | Critical Use 1 | 40 |
ABC | Critical Use 2 | 40 |
ABC | Critical USe 3 | 30 |
ABC | Critical Use | 33.6 |
ABC | Critical Use Calc | Adopting |
ABC | Creative 1 | 40 |
ABC | Creative 2 | 60 |
ABC | Creative_Calc | 50 |
ABC | Creative | Adopting |
I cannot use the transform function in Query editor because the calculated columns wouldnt appear there. What would be an alternative way to get this displayed?
Solved! Go to Solution.
Hi @arajan111
In this case, it is better to use Power Query to merge data in order to bring numerical values in your table, and unpivot the wide table after merging the additional numerical columns. Transforming your data to a long narrow table (the type of table which dax can efficienty process and consume), is Power Query's forte.
Best regards,
Hi @arajan111
In this case, it is better to use Power Query to merge data in order to bring numerical values in your table, and unpivot the wide table after merging the additional numerical columns. Transforming your data to a long narrow table (the type of table which dax can efficienty process and consume), is Power Query's forte.
Best regards,
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
86 | |
84 | |
66 | |
61 | |
59 |
User | Count |
---|---|
194 | |
111 | |
105 | |
79 | |
71 |