Append Columns into Column Sets

[EllieSim]

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:

[mahoneypat · Accepted solution]

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"

[EllieSim]

Fantastic, thank you, learnt something new - index/divided column. 🙂