Convert Rows in Columns

[roscas]

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?

 

[AlB · Accepted solution]

@roscas 

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]

@roscas 

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.

[roscas]

Hi thanks for your help, the solutions (formulas) are created on DAX? 

Is there a solution to do on M (Power Query)? Thanks

[AlB]

@roscas 

---

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

[AlB]

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.

[roscas]

Hi thanks for your prompt response, but I didn't get it, I have 

from this data

 I need a Final Table like: