Forum Discussion
roscas
Helper I
Helper IConvert Rows in Columns
I need to convert rows to Columns. If you see the picture on the left, I have the data on the right of the result, could someone please help me with this?
or if you prefer to do this in PQ, you can pivot the Salary type column:
and choose Amount as the values column:
See it all at work in the attached file. (OutputT is the version in PQ)
Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
AlB
Community Champion
Community ChampionHi roscas
Just select the two columns (Actual and Benefit) and unpivot them. Then change column names as required
Place the following M code in a blank query to see the steps.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfJLzE0F0Y7JJZllqUCGkYEBkDQGkrE60UpGUCVGyErMjUFKzIwhSoyhSoyRlZgagZQAFcQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Name = _t, Status = _t, Actual = _t, Benefit = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Status", type text}, {"Actual", Int64.Type}, {"Benefit", Int64.Type}}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"ID", "Name", "Status"}, "Attribute", "Value"),
#"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Salary type"}, {"Value", "Amount"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"ID", "Name", "Status", "Amount", "Salary type"})
in
#"Reordered Columns"
|
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
roscasHi thanks for your prompt response, but I didn't get it, I have
from this data
I need a Final Table like: