Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
roscas
Helper I
Helper I

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?

PQ 1.png

1 ACCEPTED SOLUTION
AlB
Super User
Super User

@roscas 

or if you prefer to do this in PQ, you can pivot the Salary type column:

AlB_1-1673423439435.png

and choose Amount as the values column:

AlB_2-1673423540941.png

See it all at work in the attached file. (OutputT is the version in PQ)

 

SU18_powerbi_badge

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.

 

View solution in original post

6 REPLIES 6
AlB
Super User
Super User

@roscas 

or if you prefer to do this in PQ, you can pivot the Salary type column:

AlB_1-1673423439435.png

and choose Amount as the values column:

AlB_2-1673423540941.png

See it all at work in the attached file. (OutputT is the version in PQ)

 

SU18_powerbi_badge

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
Super User
Super User

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

AlB_0-1673423187160.png

 

SU18_powerbi_badge

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 


@roscas wrote:

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


looks like you have not seen my other message,  have you?

 


SU18_powerbi_badge

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
Super User
Super User

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"

 

 

AlB_1-1673380830915.png

SU18_powerbi_badge

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

PQ3.png

 I need a Final Table like:

PQ2.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors