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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
anshpalash
Helper II
Helper II

Transforming Table

Hi,

 

I have a table similar to format: 

Part Parameter Value
1Location TX
1Rejected0
1ID12
2Location MO
2Rejected1
2ID15
3Location CA
3Rejected0
3ID21

 

I want to convert this to a format:

 

PartLocationRejectedID
1TX012
2MO115
3CA021

 

Can somebody please tell me how to approach this problem? I woud really appreciate any insights. Thank you!

1 ACCEPTED SOLUTION
sm_talha
Resolver II
Resolver II

You can try the following steps in the Power Query (Transform section on top):

1: select Value column and select "Pivot Column"
2: Go the "Use First Row as Headers" option and select "Use Headers as Row"

3: Now Transpose the table

4: Go the "Use First Row as Headers" option and select "Use First Row as Headers". 

5: It is done, but you can do more adjustments like the ordering and renaming the columns. 

 

sm_talha_0-1631033822987.png

 

 

Here is the M Query:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLJT04syczPUwCyQyKUYnUgwkGpWanJJakpQKYBXNDTBUgYGoH5Rmh6ff3hwkh6DeGCEL2mYL4xml5nR7gwhr3GML1GQLNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Part " = _t, #"Parameter " = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Part ", Int64.Type}, {"Parameter ", type text}, {"Value", type text}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Part ", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Part ", type text}}, "en-US")[#"Part "]), "Part ", "Value"),
#"Demoted Headers" = Table.DemoteHeaders(#"Pivoted Column"),
#"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
#"Transposed Table" = Table.Transpose(#"Changed Type1"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Parameter ", Int64.Type}, {"ID", Int64.Type}, {"Location ", type text}, {"Rejected", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Parameter ", "Part"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Part", "Location ", "Rejected", "ID"})
in
#"Reordered Columns"

 



View solution in original post

2 REPLIES 2
v-yangliu-msft
Community Support
Community Support

Hi  @anshpalash ,

Here are the steps you can follow:

1. In Power query, Add Column – Index Column – From 1.

vyangliumsft_0-1631237955462.png

2. Create calculated column.

rank = RANKX(FILTER(ALL('Table'),'Table'[Part ]=EARLIER('Table'[Part ])),'Table'[Index],,ASC)

3. Create calculated table.

Table 2 =
SUMMARIZE('Table','Table'[Part ],
"Location",CALCULATE(MAX('Table'[Value]),FILTER(ALL('Table'),[Part ]=EARLIER('Table'[Part ])&&'Table'[Index]=MIN('Table'[Index]))),
"Reject",CALCULATE(MAX('Table'[Value]),FILTER(ALL('Table'),[Part ]=EARLIER('Table'[Part ])&&'Table'[Index]>MIN('Table'[Index])&&'Table'[Index]<MAX('Table'[Index]))),
"ID",CALCULATE(MAX('Table'[Value]),FILTER(ALL('Table'),[Part ]=EARLIER('Table'[Part ])&&'Table'[Index]=MAX('Table'[Index]))))

4. Result:

vyangliumsft_1-1631237955462.png

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

sm_talha
Resolver II
Resolver II

You can try the following steps in the Power Query (Transform section on top):

1: select Value column and select "Pivot Column"
2: Go the "Use First Row as Headers" option and select "Use Headers as Row"

3: Now Transpose the table

4: Go the "Use First Row as Headers" option and select "Use First Row as Headers". 

5: It is done, but you can do more adjustments like the ordering and renaming the columns. 

 

sm_talha_0-1631033822987.png

 

 

Here is the M Query:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUfLJT04syczPUwCyQyKUYnUgwkGpWanJJakpQKYBXNDTBUgYGoH5Rmh6ff3hwkh6DeGCEL2mYL4xml5nR7gwhr3GML1GQLNiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Part " = _t, #"Parameter " = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Part ", Int64.Type}, {"Parameter ", type text}, {"Value", type text}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"Part ", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Part ", type text}}, "en-US")[#"Part "]), "Part ", "Value"),
#"Demoted Headers" = Table.DemoteHeaders(#"Pivoted Column"),
#"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
#"Transposed Table" = Table.Transpose(#"Changed Type1"),
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
#"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Parameter ", Int64.Type}, {"ID", Int64.Type}, {"Location ", type text}, {"Rejected", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Parameter ", "Part"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"Part", "Location ", "Rejected", "ID"})
in
#"Reordered Columns"

 



Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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