Forum Discussion
Post ProdigyTurning 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.
And this is my expected end result:
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
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"
9 Replies
- Gabry
Super User
Why? what's the issue with unpivot?
You have to select the 3 columns and select unpivot columns
Justas4478Gabry This is the result when I try to unpivot:
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.- Gabry
Why did you unpivot the other columns? Can you load the PBIX?
or sample data
- Gabry
I don't know how to upload the pbix, I think it's not possible. Please mark as accepted solution