topic Re: Unpivot two different set of columns simultaneously in Power Query

Unpivot two different set of columns simultaneously

Khalefa — Sun, 25 Dec 2022 11:53:39 GMT

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

Re: Unpivot two different set of columns simultaneously

Anonymous — Sun, 25 Dec 2022 20:15:34 GMT

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

Re: Unpivot two different set of columns simultaneously

wdx223_Daniel — Mon, 26 Dec 2022 03:28:56 GMT

Re: Unpivot two different set of columns simultaneously

Khalefa — Mon, 26 Dec 2022 08:33:34 GMT

Anonymous Thanks alot, i will try it out.

Re: Unpivot two different set of columns simultaneously

Khalefa — Mon, 26 Dec 2022 08:41:53 GMT

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

Re: Unpivot two different set of columns simultaneously

AlB — Mon, 26 Dec 2022 09:13:38 GMT

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.

Re: Unpivot two different set of columns simultaneously

Khalefa — Mon, 26 Dec 2022 10:12:49 GMT

AlB Brilliant , thanks a million