Unpivot columns but create two value columns

[DarylRob]

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,

[bcdobbs · Accepted 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.

[DarylRob]

Perfect, that's two today 😄 thanks mate

[bcdobbs]

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.