Conditional Merge of rows pairs in single column

[Anonymous]

Hello,

I have really bad data and I'm not even sure this is possible but looking to find out if there is a way to merge rows in pairs of random strings (see below). The excel data created two rows of data for this column. Each pair is different and Im not sure how to target them without affecting the rest of the row

[ronrsnfld · Accepted solution]

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.

1. You can add an Index column and then a custom column with a formula that
   1. If Index is even, then concatenate the value in Column 1 with the value in the next row
   2. else write a null
2. You can create two lists -- one of the odd numbered rows, one of the even numbered rows.
   1. Then Combine the two lists and Zip the result with a list of nulls
   2. 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"

[ronrsnfld · Accepted solution]

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.

1. You can add an Index column and then a custom column with a formula that
   1. If Index is even, then concatenate the value in Column 1 with the value in the next row
   2. else write a null
2. You can create two lists -- one of the odd numbered rows, one of the even numbered rows.
   1. Then Combine the two lists and Zip the result with a list of nulls
   2. 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"

[Anonymous]

@ronrsnfldthanksso much for this help!

either list.zip or list.combine is removing the promoted headers from the original table hence this last bit of code is causing an error. Any ideas?

let
    Source = Folder.Files("C:\Users\O81465\Downloads\OneDrive_2023-01-20\Mexico Ruben Dario - Control de Acceso\Mexico R&D - Control de Acceso\R&D 2022"),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Expanded Transform File" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}, {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Transform File", [PromoteAllScalars=true]),
    
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Personnal Category", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Personnal Category] = "PRESTADOR DE " or [Personnal Category] = "SERVICIOS EXTERNO" or [Personnal Category] = "SERVICIOS INTERNO")),
    #"Even Rows" = List.Alternate(#"Filtered Rows"[Personnal Category],1,1,1),
    #"Odd Rows" = List.Alternate(#"Filtered Rows"[Personnal Category],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"))}))
in
    #"Alternate with nulls"

[ronrsnfld]

I haven't run it, but this last bit of code does not even end with a table. Hard to tell what's going on

[Anonymous]

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.

[ronrsnfld · Accepted solution]

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"))

[Anonymous]

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!!

[ronrsnfld]

I 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"))