Hi teamjust 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 IDCheck ValidateConfirmCheck Root causeValidate Root CauseConfirm Root CauseA10000 MissingLackB01000Missing MissingC01000LackMissing 1st table 2nd table ( desired one ) Review IDAttributeValueAttribute Root CauseValue2ACheck100Check Root cause AValidate0Validate Root CausemissingAConfirm0Confirm Root CauselackBCheck0Check Root causemissingBValidate100Validate Root Cause BConfirm0Confirm Root CausemissingCCheck0Check Root causelackCValidate0Validate Root CausemissingCConfirm100Confirm Root Cause

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 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

Need Help

AlB

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.

Unpivot two different set of columns simultaneously.pbix23 KB

6 Replies

AlB

Community Champion

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.

Unpivot two different set of columns simultaneously.pbix23 KB

Khalefa
Helper I
3 years ago
AlB Brilliant , thanks a million

wdx223_Daniel
Community Champion
3 years ago
- Khalefa
  Helper I
  3 years ago
  wdx223_Daniel Awesome but I'm really curious to understand the logic and if you can provide the whole M code
Anonymous
Not applicable
3 years ago
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
  Helper I
  3 years ago
  Anonymous Thanks alot, i will try it out.

Forum Discussion

Unpivot two different set of columns simultaneously

6 Replies

Related content

Pivot / Unpivot?

Unpivot Column

Unpivot Question

Unpivot

Unpivot columns