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 Chaps,
I've got a table like below and I want to be able to unpivot it so it creates an "Hours" column and a "MRR" column but then only one row per quarter...
Name | Q1 Hours | Q2 Hours | Q3 Hours | Q4 Hours | Q1 MRR | Q2 MRR | Q3 MRR | Q4 MRR |
Gary | 100 | 200 | 300 | 400 | 10 | 20 | 30 | 40 |
Trevor | 100 | 200 | 300 | 400 | 10 | 20 | 30 | 40 |
So I'd want an outcome like:
Name | Quarter | Hours | MRR |
Gary | Q1 | 100 | 10 |
Gary | Q2 | 200 | 20 |
Gary | Q3 | 300 | 30 |
Gary | Q4 | 400 | 40 |
Thanks,
Solved! Go to Solution.
In power query:
1) Unpivot so you end up with:
Gary, Q1 Hours, 100
Gary, Q1 MRR, 10
etc....
2) Split the column with rows like Q1 Hours and Q1 MRR into two using either column from example or split column on deliminator (space). Ending up with:
Gary, Q1, Hours, 100
Gary, Q1, MRR, 10
etc....
3) You can then pivot to get Hours and MRR as column names.
In power query:
1) Unpivot so you end up with:
Gary, Q1 Hours, 100
Gary, Q1 MRR, 10
etc....
2) Split the column with rows like Q1 Hours and Q1 MRR into two using either column from example or split column on deliminator (space). Ending up with:
Gary, Q1, Hours, 100
Gary, Q1, MRR, 10
etc....
3) You can then pivot to get Hours and MRR as column names.
Perfect, that's two today 😄 thanks mate
No worries. Once your data is in that sort of form Power Bi makes the rest much easier. Worth reading up on Star Schemas
https://docs.microsoft.com/en-us/power-bi/guidance/star-schema
and introduction to data modelling in power bi at SQLBI:
https://www.sqlbi.com/p/introduction-to-data-modeling-for-power-bi-video-course/
If you get the model right it makes the DAX much easier if needed at all.
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 |
---|---|
111 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |