Forum Discussion

anshpalash's avatar
anshpalash
Helper II
4 years ago
Solved

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 ...
Need Help
  • sm_talha's avatar
    sm_talha
    4 years ago

    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"

     



Anonymous's avatar
Anonymous
Not applicable
4 years ago

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