Forum Discussion
Append Columns into Column Sets
Hello,
I have the below table with has a number of column pair with corresponding data sets. For each column which display a classification code, there is a corresponding column which displays it's description. Is there an efficient way to regroup the table into two columns only, one with all the codes (from the 3 codes columns) in order and one with the corresponding descriptions? I did it but in a long-winded way, creating a table for each pair of columns and then appending all the tables together in the query editor. Is there an more efficient alternative through the query editor or a calculated table? Much appreciated. Many thanks 🙂
The result table should look like this:
I made some mock data to demonstrate. Here is one way to do it. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpJrSgB0YZ6MJ4RhAfnGyvF6kQrGUF5JkDaCC5nCuHB+WZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"L1-Code" = _t, #"L1-Description" = _t, #"L2-Code" = _t, #"L2-Description" = _t, #"L3-Code" = _t, #"L3-Description" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"L1-Code", type text}, {"L1-Description", type text}, {"L2-Code", type text}, {"L2-Description", type text}, {"L3-Code", type text}, {"L3-Description", type text}}), #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"L1-Code", "L1-Description", "L2-Code", "L2-Description", "L3-Code", "L3-Description"}, "Attribute", "Value"), #"Extracted Text After Delimiter" = Table.TransformColumns(#"Unpivoted Only Selected Columns", {{"Attribute", each Text.AfterDelimiter(_, "-"), type text}}), #"Added Index" = Table.AddIndexColumn(#"Extracted Text After Delimiter", "Index", 1, 1, Int64.Type), #"Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.RoundUp(_ / 2,0), type number}}), #"Pivoted Column" = Table.Pivot(#"Divided Column", List.Distinct(#"Divided Column"[Attribute]), "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}) in #"Removed Columns"
2 Replies
I made some mock data to demonstrate. Here is one way to do it. To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpJrSgB0YZ6MJ4RhAfnGyvF6kQrGUF5JkDaCC5nCuHB+WZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"L1-Code" = _t, #"L1-Description" = _t, #"L2-Code" = _t, #"L2-Description" = _t, #"L3-Code" = _t, #"L3-Description" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"L1-Code", type text}, {"L1-Description", type text}, {"L2-Code", type text}, {"L2-Description", type text}, {"L3-Code", type text}, {"L3-Description", type text}}), #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"L1-Code", "L1-Description", "L2-Code", "L2-Description", "L3-Code", "L3-Description"}, "Attribute", "Value"), #"Extracted Text After Delimiter" = Table.TransformColumns(#"Unpivoted Only Selected Columns", {{"Attribute", each Text.AfterDelimiter(_, "-"), type text}}), #"Added Index" = Table.AddIndexColumn(#"Extracted Text After Delimiter", "Index", 1, 1, Int64.Type), #"Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.RoundUp(_ / 2,0), type number}}), #"Pivoted Column" = Table.Pivot(#"Divided Column", List.Distinct(#"Divided Column"[Attribute]), "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}) in #"Removed Columns"