Turning rows in to columns

[Justas4478]

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

[Gabry]

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

[Gabry]

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

[Justas4478]

@Gabry 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

[Justas4478]

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

[Gabry]

yes

[Justas4478]

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

[Gabry · 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"

[Justas4478]

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