Forum Discussion
Unpivot columns but create two value columns
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,
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.
3 Replies
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.
