Forum Discussion
Conditional Merge of rows pairs in single column
There are several methods
Assumption is that the "merging" begins with the first row in the table (row 0)
Assumption also that there will always be an even number of rows.
- You can add an Index column and then a custom column with a formula that
- If Index is even, then concatenate the value in Column 1 with the value in the next row
- else write a null
- You can create two lists -- one of the odd numbered rows, one of the even numbered rows.
- Then Combine the two lists and Zip the result with a list of nulls
- Replace the first column with this new list.
Here is code with an example of the 2nd approach. You may need to process the column names as you don't show them.
let Source = Excel.CurrentWorkbook(){[Name="Table18"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}}), #"Even Rows" = List.Alternate(#"Changed Type"[Column1],1,1,1), #"Odd Rows" = List.Alternate(#"Changed Type"[Column1],1,1,0), #"Combine Lists" = List.Transform(List.Zip({#"Even Rows",#"Odd Rows"}), each Text.Combine(_," ")), #"Alternate with nulls" = List.Combine(List.Zip({#"Combine Lists", List.Repeat({null}, List.Count(#"Combine Lists"))})), #"Combine with Original Table" = Table.FromColumns( {#"Alternate with nulls"} & Table.ToColumns(Table.RemoveColumns(#"Changed Type",{"Column1"}))) in #"Combine with Original Table"- You can add an Index column and then a custom column with a formula that
Neither of those steps have anything at all to do with "removing the promoted headers from the original table" or with "order of the columns is not retained". I had mentioned the need to process the column names in my answer. I suggest you change the Combine with Original Table step to include the optional column name argument to obtain the desired column names.
A simple way to do that:
#"Combine with Original Table" = Table.FromColumns( {#"Alternate with nulls"} & Table.ToColumns(Table.RemoveColumns(#"Changed Type",{"Column1"})), Table.ColumnNames(#"Filtered Rows"))
The table looks like below up until the highlighted step.
After that, I inserted and edited the code you helped me with which works to merge paired rows as seen below
The last step is where I am getting stuck. The code works to merge list with orginal table, but the headers are gone and the order of the columns is not retained.
Super UserNeither of those steps have anything at all to do with "removing the promoted headers from the original table" or with "order of the columns is not retained". I had mentioned the need to process the column names in my answer. I suggest you change the Combine with Original Table step to include the optional column name argument to obtain the desired column names.
A simple way to do that:
#"Combine with Original Table" = Table.FromColumns(
{#"Alternate with nulls"} & Table.ToColumns(Table.RemoveColumns(#"Changed Type",{"Column1"})), Table.ColumnNames(#"Filtered Rows"))