Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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 .
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.
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
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
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
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |