Forum Discussion
Convert 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.
6 Replies
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.
Hi 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.
Hi thanks for your prompt response, but I didn't get it, I have
from this data
I need a Final Table like:
I misunderstood. Thought your input was your output. In that case, you can:
1. Place ID, Name and Status in a table visual
2. Create these two measures and place them in the table visual:
Actual = CALCULATE ( SUM ( InputT[Amount] ), InputT[Salary type] = "Actual" )Benefit = CALCULATE ( SUM ( InputT[Amount] ), InputT[Salary type] = "Benefit" )The result will look like:
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.
Hi thanks for your help, the solutions (formulas) are created on DAX?
Is there a solution to do on M (Power Query)? Thanks
roscas wrote:
Is there a solution to do on M (Power Query)? Thanks
looks like you have not seen my other message, have you?
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.
