Forum Discussion
Transforming Table
Hi,
I have a table similar to format:
| Part | Parameter | Value |
| 1 | Location | TX |
| 1 | Rejected | 0 |
| 1 | ID | 12 |
| 2 | Location | MO |
| 2 | Rejected | 1 |
| 2 | ID | 15 |
| 3 | Location | CA |
| 3 | Rejected | 0 |
| 3 | ID | 21 |
I want to convert this to a format:
| Part | Location | Rejected | ID |
| 1 | TX | 0 | 12 |
| 2 | MO | 1 | 15 |
| 3 | CA | 0 | 21 |
Can somebody please tell me how to approach this problem? I woud really appreciate any insights. Thank you!
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.
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"
2 Replies
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.
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"Hi anshpalash ,
Here are the steps you can follow：
1. In Power query, Add Column – Index Column – From 1.
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:
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
