Merge multiple columns into single column with multiple rows

[btcar94]

How do I merge multiple columns into a single column with multiple rows? The first image is what i have and the second is what i want. 

Location	Observation 1	Risk 1	Observation 2	Risk 2	Observation 3	Risk 3
Mexico	Shipping	Process	Sales	Process	Inventory	Process
USA	Inventory	DOA	Expense	DOA	PO Process	DOA
Argentina	PO Process	Process	Inventory	Process	Expense	Process

Location	Observation #	Observation	Risk
Mexico	1	Shipping	Process
Mexico	2	Sales	Process
Mexico	3	Inventory	Process
USA	1	Inventory	DOA
USA	2	Expense	DOA
USA	3	PO Process	DOA
Argentina	1	PO Process	Process
Argentina	2	Inventory	Process
Argentina	3	Expense	Process

[jgeddes · Accepted solution]

There are a few ways to do this. 
Here is an example code you can paste into the advanced editor of a blank query and then follow though the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k2tyEzOV9JRCs7ILCjIzEsHMgOK8pNTi4tBgok5qcUoIp55Zal5JflFlUiisTrRSqHBjmiyLv4gEdeKgtS84lQ4P8BfAWEYSAik2bEoHagtMy8RXQF+e5FNh7slFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Location = _t, #"Observation 1" = _t, #"Risk 1" = _t, #"Observation 2" = _t, #"Risk 2" = _t, #"Observation 3" = _t, #"Risk 3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Observation 1", type text}, {"Risk 1", type text}, {"Observation 2", type text}, {"Risk 2", type text}, {"Observation 3", type text}, {"Risk 3", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Location"}, "Observation Number", "Value"),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Observation Number", each Text.AfterDelimiter(_, " "), type text}}),
    #"Grouped Rows" = Table.Group(#"Extracted Text After Delimiter", {"Location", "Observation Number"}, {{"_nestedTable", each Table.SelectColumns(_, {"Value"}), type table [Location=nullable text, Observation=text, Value=text]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"_nestedTable", each Table.Transpose(_)}}),
    #"Expanded _nestedTable" = Table.ExpandTableColumn(Custom1, "_nestedTable", {"Column1", "Column2"}, {"Observation", "Risk"})
in
    #"Expanded _nestedTable"

[Anonymous · Accepted solution]

Hi @btcar94 

Thanks to @jgeddes  for the excellent solution. 

Here is an alternative solution if you have interest:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k2tyEzOV9JRCs7ILCjIzEsHMgOK8pNTi4tBgok5qcUoIp55Zal5JflFlUiisTrRSqHBjmiyLv4gEdeKgtS84lQ4P8BfAWEYSAik2bEoHagtMy8RXQF+e5FNh7slFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Location = _t, #"Observation 1" = _t, #"Risk 1" = _t, #"Observation 2" = _t, #"Risk 2" = _t, #"Observation 3" = _t, #"Risk 3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Observation 1", type text}, {"Risk 1", type text}, {"Observation 2", type text}, {"Risk 2", type text}, {"Observation 3", type text}, {"Risk 3", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.AddIndexColumn(Table.FromRows(List.Split(List.RemoveFirstN(Record.ToList(_),1),2), {"Observation","Risk"}),"Observation #",1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Location", "Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Observation", "Risk", "Observation #"}, {"Observation", "Risk", "Observation #"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom",{"Location", "Observation #", "Observation", "Risk"})
in
    #"Reordered Columns"

The first and key step is to add a custom column with below code. Then remove columns from "Observation 1" to "Risk 3". Expand the custom column and reorder columns. 

Table.AddIndexColumn(Table.FromRows(List.Split(List.RemoveFirstN(Record.ToList(_),1),2), {"Observation","Risk"}),"Observation #",1,1)

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[AlienSx · Accepted solution]

let
    Source = what_you_have,
    unpivot = Table.UnpivotOtherColumns(Source, {"Location"}, "Attribute", "Value"),
    split = Table.SplitColumn(
        unpivot, "Attribute", 
        Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), 
        {"Attribute.1", "Observation #"}
    ),
    pivot = Table.Pivot(
        split, 
        List.Distinct(split[Attribute.1]), 
        "Attribute.1", "Value"
    )
in
    pivot

[Anonymous · Accepted solution]

Hi @btcar94 

Thanks to @jgeddes  for the excellent solution. 

Here is an alternative solution if you have interest:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k2tyEzOV9JRCs7ILCjIzEsHMgOK8pNTi4tBgok5qcUoIp55Zal5JflFlUiisTrRSqHBjmiyLv4gEdeKgtS84lQ4P8BfAWEYSAik2bEoHagtMy8RXQF+e5FNh7slFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Location = _t, #"Observation 1" = _t, #"Risk 1" = _t, #"Observation 2" = _t, #"Risk 2" = _t, #"Observation 3" = _t, #"Risk 3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Observation 1", type text}, {"Risk 1", type text}, {"Observation 2", type text}, {"Risk 2", type text}, {"Observation 3", type text}, {"Risk 3", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.AddIndexColumn(Table.FromRows(List.Split(List.RemoveFirstN(Record.ToList(_),1),2), {"Observation","Risk"}),"Observation #",1,1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Location", "Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Observation", "Risk", "Observation #"}, {"Observation", "Risk", "Observation #"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Custom",{"Location", "Observation #", "Observation", "Risk"})
in
    #"Reordered Columns"

The first and key step is to add a custom column with below code. Then remove columns from "Observation 1" to "Risk 3". Expand the custom column and reorder columns. 

Table.AddIndexColumn(Table.FromRows(List.Split(List.RemoveFirstN(Record.ToList(_),1),2), {"Observation","Risk"}),"Observation #",1,1)

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[jgeddes · Accepted solution]

There are a few ways to do this. 
Here is an example code you can paste into the advanced editor of a blank query and then follow though the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k2tyEzOV9JRCs7ILCjIzEsHMgOK8pNTi4tBgok5qcUoIp55Zal5JflFlUiisTrRSqHBjmiyLv4gEdeKgtS84lQ4P8BfAWEYSAik2bEoHagtMy8RXQF+e5FNh7slFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Location = _t, #"Observation 1" = _t, #"Risk 1" = _t, #"Observation 2" = _t, #"Risk 2" = _t, #"Observation 3" = _t, #"Risk 3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", type text}, {"Observation 1", type text}, {"Risk 1", type text}, {"Observation 2", type text}, {"Risk 2", type text}, {"Observation 3", type text}, {"Risk 3", type text}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Location"}, "Observation Number", "Value"),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", {{"Observation Number", each Text.AfterDelimiter(_, " "), type text}}),
    #"Grouped Rows" = Table.Group(#"Extracted Text After Delimiter", {"Location", "Observation Number"}, {{"_nestedTable", each Table.SelectColumns(_, {"Value"}), type table [Location=nullable text, Observation=text, Value=text]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"_nestedTable", each Table.Transpose(_)}}),
    #"Expanded _nestedTable" = Table.ExpandTableColumn(Custom1, "_nestedTable", {"Column1", "Column2"}, {"Observation", "Risk"})
in
    #"Expanded _nestedTable"