Pivot / Unpivot columns 8 columns into rows

[Matt22365]

Hi

I hope someone can help

I want to make a matrix which shows the amount of "Resource type 1", "Resource type 2", "Resource 1 costs", "Resource 2 costs" by Team over the years (based on start date). I am not sure which columns to unpivot / pivot on to get this view.

Picture shows simple version of my data 

This is what I would like to have

Thanks for your help

[v-easonf-msft]

Hi , @Matt22365 

Try steps as below:

1.Click "Insert step after" and enter formula as below:

= Table.UnpivotOtherColumns(#"Changed Type", {"Team", "Start Date"}, "Attribute", "Value")

it will show as below then click "close &apply"

3.Then create matrix as below

Here is a demo .

pbix attached

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[vivran22 · Accepted solution]

Hello @Matt22365,

You may try following:

Insert Year column

Select the four columns > Unpivot column

Transformed Result

Use Matrix visual:

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

[camargos88]

Hi @Matt22365 ,

Just paste this code on Advanced Editor:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCklNzFUwVNJRMjDUByIjA0NLIMcUiA0NgISRUqwOVJERkiIjkJwhSJUxiGWCUGWMrApkrinYGBBhoRQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Team = _t, #"Start Date" = _t, #"Resource Type 1" = _t, #"Resource Type 2" = _t, #"Resource Type 3" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Team", type text}, {"Start Date", type date}, {"Resource Type 1", Int64.Type}, {"Resource Type 2", Int64.Type}, {"Resource Type 3", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Team", "Start Date"}, "Attribute", "Value")
in
#"Unpivoted Columns"

Ricardo

[AilleryO]

Hi,

First you should transform your Date Column into Year using Power Query and the Date button will give you, in M code :

ExtractYear = Table.TransformColumns(ChangeType,{{"Start Date", Date.Year, Int64.Type}})

Then select all columns except the 2 first (Team and Year), and use the Unpivot button, choose Unpivot only selected columns.

Will give you this M code :

= Table.Unpivot(ExtractYear, {"Ress Type 1", "Ress Type 2", "Ress Cost 1", "Ress Cost 2"}, "Attribut", "Valeur")

The you should have what you need for your calculation.

Hope it helps