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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Justas4478
Post Prodigy
Post Prodigy

Turning rows in to columns

Hello, I am trying to turn som rows in to columns. In a sense opposite of unpivot.
This is table that I have.

Justas4478_0-1710520424677.png

And this is my expected end result:

Justas4478_1-1710520488453.png

Its really simple but I cant seam to make it work.
In a sense there are 5 different 'RATE' and each of them has one 'PAYRATE' and 'CHGRATE'.

I tried using unpivot but that is obviously wrong choice.
I thought that maybe grouping could be a solution but I cant seam to make it work.

 

Anyone has any idea how I can do this?

Thanks

1 ACCEPTED SOLUTION

this is the M code to do the transformation

let
Source = Excel.Workbook(File.Contents("C:\Users\----\Downloads\Sample data.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Agency", type text}, {"Dept/Shift", type text}, {"RATE", type text}, {"PAYRATE", type number}, {"CHGRATE", type number}}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"PAYRATE", "CHGRATE"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Only Selected Columns", "Custom", each [RATE]& " " &
[Attribute]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"RATE", "Attribute"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value", List.Sum)
in
#"Pivoted Column"

View solution in original post

9 REPLIES 9
Gabry
Super User
Super User

I don't know how to upload the pbix, I think it's not possible. Please mark as accepted solution

Gabry
Super User
Super User

Why? what's the issue with unpivot?
You have to select the 3 columns and select unpivot columns

@Gabry This is the result when I try to unpivot:

Justas4478_0-1710521704434.png

As you see it is not in multiple columns as I need it to be.
The other reason why I need it to be as in expected example is because I have other table that has multiple agency that I am going to merge this table to and it needs to match columns of that table.

Why did you unpivot the other columns? Can you load the PBIX?

 

or sample data

@Gabry I can't provide the pbix file only excel with data in the main table is that good enough?

yes

@Gabry Here is sample file 
https://we.tl/t-8wk6y2VIu9 

this is the M code to do the transformation

let
Source = Excel.Workbook(File.Contents("C:\Users\----\Downloads\Sample data.xlsx"), null, true),
Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Agency", type text}, {"Dept/Shift", type text}, {"RATE", type text}, {"PAYRATE", type number}, {"CHGRATE", type number}}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"PAYRATE", "CHGRATE"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Only Selected Columns", "Custom", each [RATE]& " " &
[Attribute]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"RATE", "Attribute"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom]), "Custom", "Value", List.Sum)
in
#"Pivoted Column"

@Gabry It worked thank you.
I dont know how I missed Pivot function.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.