Forum Discussion

Khalefa's avatar
Khalefa
Helper I
3 years ago
Solved

Unpivot two different set of columns simultaneously

Hi team just need some help to convert the 1st table to the second table format. I tried to do unpivot in two steps but it didn't give me the desired format.   I also tried to use (Table.from col...
  • AlB's avatar
    3 years ago

    Hi Khalefa 

    Place the following M code in a blank query to see the steps. See it all at work in the attached file

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MACSMAxEvpnFxZl56UCWT2JytlKsTrSSE1QWoRahCIkDUuqMoRRsCoqO2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Review ID" = _t, #"Check " = _t, Validate = _t, Confirm = _t, #"Check Root cause" = _t, #"Validate Root Cause" = _t, #"Confirm Root Cause" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Review ID", type text}, {"Check ", Int64.Type}, {"Validate", Int64.Type}, {"Confirm", Int64.Type}, {"Check Root cause", type text}, {"Validate Root Cause", type text}, {"Confirm Root Cause", type text}}),
        piece1 = Table.SelectColumns(#"Changed Type", {"Review ID", "Check ", "Validate", "Confirm"}),
    
    
        #"Unpivot piece1" = Table.UnpivotOtherColumns(piece1, {"Review ID"}, "Attribute", "Value"),
        #"Added Index" = Table.AddIndexColumn(#"Unpivot piece1", "Index", 1, 1, Int64.Type),
        piece2 = Table.SelectColumns(#"Changed Type", {"Review ID", "Check Root cause", "Validate Root Cause", "Confirm Root Cause"}),
        #"Unpivot piece2" = Table.UnpivotOtherColumns(piece2, {"Review ID"}, "Attribute Root Cause", "Value2"),
        #"Added Index2" = Table.AddIndexColumn(#"Unpivot piece2", "Index", 1, 1, Int64.Type),
        #"Merged Queries" = Table.NestedJoin(#"Added Index", {"Index"}, #"Added Index2", {"Index"}, "Unpivot piece2", JoinKind.LeftOuter),
        #"Expanded Unpivot piece2" = Table.ExpandTableColumn(#"Merged Queries", "Unpivot piece2", {"Attribute Root Cause", "Value2"}, {"Attribute Root Cause", "Value2"}),
        #"Removed Columns" = Table.RemoveColumns(#"Expanded Unpivot piece2",{"Index"})
    in
        #"Removed Columns"

    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.