Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

Conditional Merge of rows pairs in single column

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

 

Kentucky_0-1674292057966.png

 

2 ACCEPTED SOLUTIONS
ronrsnfld
Super User
Super User

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_0-1674350987465.png

 

View solution in original post

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

 

View solution in original post

7 REPLIES 7
ronrsnfld
Super User
Super User

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_0-1674350987465.png

 

Anonymous
Not applicable

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

 

 

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
Not applicable

The table looks like below up until the highlighted step.

Kentucky_3-1674427370301.png

After that, I inserted and edited the code you helped me with which works to merge paired rows as seen below

Kentucky_4-1674427468288.png

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.

Kentucky_5-1674427512688.png

 

 

 

 

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
Not applicable

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

 

Kentucky_0-1674407943746.png

 

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

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors