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