Transforming Table

[anshpalash]

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!

[v-yangliu-msft]

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

[sm_talha · Accepted solution]

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"