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"))
Hi ronrsnfld Thank you so much for this! It almost worked but Im having some issue with processing the code. In the code below, I have removed other columns to leave the two I need to fix "Personnal Category" and "Name". Once I figure out how to insert the list guidance you provided, I can then remove the step of "remove other columns" and my table will be perfect!
My question is, can I use list. alternate with 2 columns at the same time?
I need to move second row up and concatenate with first row in both columns then replace second row with null
Thank you so much for the help!!
Super UserI don't know why I didn't see this message until this morning.
And the answer is Yes, you can.
Below is not tested and you may have to make some changes in the #"Combine..." step
if your columns to combine are not contiguous
Duplicate the four steps that create the list (obviously with different names) referencing the other column:
#"Even Rows1" = List.Alternate(#"Changed Type"[Column2],1,1,1),
#"Odd Rows1" = List.Alternate(#"Changed Type"[Column2],1,1,0),
#"Combine Lists1" = List.Transform(List.Zip({#"Even Rows1",#"Odd Rows1"}), each Text.Combine(_," ")),
#"Alternate with nulls1" = List.Combine(List.Zip({#"Combine Lists1", List.Repeat({null}, List.Count(#"Combine Lists1"))})),
Then change the #"Combine with Original Table" step to incorporate the new column, and delete its source column:
#"Combine with Original Table" = Table.FromColumns(
{#"Alternate with nulls"} & {#"Alternate with nulls1"} & Table.ToColumns(Table.RemoveColumns(#"Changed Type",{"Column1","Column2"})), Table.ColumnNames(#"Filtered Rows"))