Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Matt22365
Resolver III
Resolver III

Pivot / Unpivot columns 8 columns into rows

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 

Picture1.png

This is what I would like to have

Picture2.png

Thanks for your help

1 ACCEPTED SOLUTION
vivran22
Community Champion
Community Champion

Hello @Matt22365,

 

You may try following:

 

Insert Year column

Insert Year.PNG

Select the four columns > Unpivot column

3.png

 

Transformed Result

4.PNG

Use Matrix visual:

Capture.PNG

 

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

View solution in original post

4 REPLIES 4
v-easonf-msft
Community Support
Community Support

Hi , @Matt22365 

Try steps as below:

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

64.png

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

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

65.png

 

3.Then create matrix as below

66.png

 

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
Community Champion
Community Champion

Hello @Matt22365,

 

You may try following:

 

Insert Year column

Insert Year.PNG

Select the four columns > Unpivot column

3.png

 

Transformed Result

4.PNG

Use Matrix visual:

Capture.PNG

 

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
Community Champion
Community Champion

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"

 

Capturar.PNG

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



AilleryO
Memorable Member
Memorable Member

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.

TempPBI01.png

Hope it helps

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.