Unpivot two different set of columns simultaneously

[Khalefa]

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 columns function) to expand both groups at once, but it didn't work

Review ID	Check	Validate	Confirm	Check Root cause	Validate Root Cause	Confirm Root Cause
A	100	0	0		Missing	Lack
B	0	100	0	Missing		Missing
C	0	100	0	Lack	Missing

1st table

2nd table ( desired one )

Review ID	Attribute	Value	Attribute Root Cause	Value2
A	Check	100	Check Root cause
A	Validate	0	Validate Root Cause	missing
A	Confirm	0	Confirm Root Cause	lack
B	Check	0	Check Root cause	missing
B	Validate	100	Validate Root Cause
B	Confirm	0	Confirm Root Cause	missing
C	Check	0	Check Root cause	lack
C	Validate	0	Validate Root Cause	missing
C	Confirm	100	Confirm Root Cause

[AlB · Accepted solution]

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.

[Khalefa]

@AlB Brilliant , thanks a million 

[wdx223_Daniel]

[Khalefa]

@wdx223_Daniel Awesome but I'm really curious to understand the logic and if you can provide the whole M code

[Anonymous]

I would break this into two tables, one referencing the first four columns, and one referencing (or selecting from a reference query) the first, fifth, sixth, and seventh columns. For each table, select the first column and choose "Unpivot other columns".  Then Left Join them back together.

--Nate

[Khalefa]

@Anonymous  Thanks alot, i will try it out.