Forum Discussion
Khalefa
3 years agoHelper I
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...
- 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.
wdx223_Daniel
3 years agoCommunity Champion
- Khalefa3 years agoHelper I
wdx223_Daniel Awesome but I'm really curious to understand the logic and if you can provide the whole M code