Helper V

1 year ago

Solved

Merge 2 Rows

Hi,

I have looked a few posts abour merging 2 rows but every posts had its subtility that I could not apply.

Basically, I have a table of a list of items, every row corresponds to 1 item. However, some products are spread over 2 rows.

I have found a pattern to isolate these products: if Text.Length([Column3]) = 24 then 1 else 0

if Text.Length([Column3]) = 24 then 1 else 0

Then, I grouped by my table by 1 and 0.

I have now a table with 2 nested tables.

I am interested in merging the rows of the Table 0, every two rows should be merged with a space delimiter

row 1 and row 2 should become row 1

row 3 and row 4 should become row 2

row 5 and row 6 should become row 3

etc..

I will appreciate any help, I'd like a solution leveraging Table.TransformColumn so that I can perform all steps into the nested table

Thanks for your time and help

dufoq3 Omid_Motamedise lbendlin AlienSx ronrsnfld

AlienSx much better than my approach. Just a small addition

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZbbbtswDIZfheh1i8mn2L5MXCDrmm7GXKAY0l6ojhYLcaROttBmTz/6mHhuTl6XCzNmFPHXJ5LSfH7xXdIshyDh8YoJmLElGJ9suLi8EDpN0YwnAVzV9pqlHAKpRc4UugyCH8PyPLv78nQ5b/5dGwyygChXjOX4dsdomsGjJsQcQcQXDK55lrAMf7KJ/5hXtpymiDrWz0wtuWAQ8l+aihxuZyGOMQmZNgKhiLCrMlTsKqS4pgWELKNxgh650HGe7Wo1wfHdvqO/gFZ7112aRudEUZ5hvIA+P9MlK1exVfi+uHIZhucVgw3T8I+y6+J6T0azlYFWalOjst0PQOXapO8YhipIFN3ghvY3ci+mbdzuyzFg/SV5llk/GznXOl5BJPULzKgqd243+Q8BK/6UQSf9wXbsvmMYp8+yLM0Ug9blMqUq5fHZ0M6s0IimdFGy8oz62UgqCUH0igNhJuMVZqKAW71k6RnYyvHF/Baxbb+1wyDdsTceUyHhC1OrzYHO1UVjmI7V2mNAgoSqWGPb42yfCp0lSso1FufLOQnUkG53xwDfH/Udw9iE2AtSDHMvxVKzatYpHIWDjYt0NAwk5FveVorMaS6rBX9cB7fAMkjfMZBWiNUmU4ZHTF1uTWI7zn+stjvKRbEoZ0Tq546gqcLyLxhg3fEN9oMbAd8ElwJJ6rg8ZZzTKDZhWmUEXOL1HYPZ1Ts8SbnIf1fHOYTj4PZ4uhmmbZLtl38/BFFNhEmzZu1xGMWJ4CgrBSssFFXP06EZhkVaOwxRddWaMPazmOikjMI7Qed+MrQOibtVobOMU9GtR6xEWFYVCSclU79vOZg8Vt8xDFW0lnWUtRR/6zpQgL7rtraYK6Q5KwbqN7YPWLuSd3WU59xYlCe92nO1O8ipk6o7Pd0aGX3HMFj3Wq3YBjtEliu65iefgAfaVUTF4pVjz31IOBKcKFaucA/AauwlPCj6sr3YCwxUNgHPqZ+N4B9szUQxLxalXp5xWn5lr3CTs3V15TJM023tmeie/gA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"null",null,Replacer.ReplaceValue,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}),
    lst = List.Split(Table.ToRows(#"Replaced Value"), 2),
    trn = List.Transform(lst, (x) => ((w) => List.Transform(w, (z) => Text.Combine(z, " ")))(List.Zip(x))),
    tbl = Table.FromRows(trn, Value.Type(#"Replaced Value"))
in
    tbl

14 Replies

dufoq3

Community Champion

1 year ago

Hi Einomi, another solution:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZbbbtswDIZfheh1i8mn2L5MXCDrmm7GXKAY0l6ojhYLcaROttBmTz/6mHhuTl6XCzNmFPHXJ5LSfH7xXdIshyDh8YoJmLElGJ9suLi8EDpN0YwnAVzV9pqlHAKpRc4UugyCH8PyPLv78nQ5b/5dGwyygChXjOX4dsdomsGjJsQcQcQXDK55lrAMf7KJ/5hXtpymiDrWz0wtuWAQ8l+aihxuZyGOMQmZNgKhiLCrMlTsKqS4pgWELKNxgh650HGe7Wo1wfHdvqO/gFZ7112aRudEUZ5hvIA+P9MlK1exVfi+uHIZhucVgw3T8I+y6+J6T0azlYFWalOjst0PQOXapO8YhipIFN3ghvY3ci+mbdzuyzFg/SV5llk/GznXOl5BJPULzKgqd243+Q8BK/6UQSf9wXbsvmMYp8+yLM0Ug9blMqUq5fHZ0M6s0IimdFGy8oz62UgqCUH0igNhJuMVZqKAW71k6RnYyvHF/Baxbb+1wyDdsTceUyHhC1OrzYHO1UVjmI7V2mNAgoSqWGPb42yfCp0lSso1FufLOQnUkG53xwDfH/Udw9iE2AtSDHMvxVKzatYpHIWDjYt0NAwk5FveVorMaS6rBX9cB7fAMkjfMZBWiNUmU4ZHTF1uTWI7zn+stjvKRbEoZ0Tq546gqcLyLxhg3fEN9oMbAd8ElwJJ6rg8ZZzTKDZhWmUEXOL1HYPZ1Ts8SbnIf1fHOYTj4PZ4uhmmbZLtl38/BFFNhEmzZu1xGMWJ4CgrBSssFFXP06EZhkVaOwxRddWaMPazmOikjMI7Qed+MrQOibtVobOMU9GtR6xEWFYVCSclU79vOZg8Vt8xDFW0lnWUtRR/6zpQgL7rtraYK6Q5KwbqN7YPWLuSd3WU59xYlCe92nO1O8ipk6o7Pd0aGX3HMFj3Wq3YBjtEliu65iefgAfaVUTF4pVjz31IOBKcKFaucA/AauwlPCj6sr3YCwxUNgHPqZ+N4B9szUQxLxalXp5xWn5lr3CTs3V15TJM023tmeie/gA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    ChangedTypeToTextDynamic = Table.TransformColumns(Source, {}, Text.From),
    Transformed = List.TransformMany(Table.Split(ChangedTypeToTextDynamic, 2),
            each {List.Transform(Table.ColumnNames(ChangedTypeToTextDynamic), (x)=> Text.Combine(Table.Column(_, x), " "))},
            (x,y)=> y ),
    ToTable = Table.FromRows(Transformed, Value.Type(ChangedTypeToTextDynamic))
in
    ToTable

Einomi
Helper V
1 year ago
Thanks dufoq3 I will test your solution a bit later
- dufoq3
  Community Champion
  1 year ago
  My solution is similar to AlienSx, but I've added dynamic changed type to text as 2nd step to not get same error as you mentioned below AlienSx's post.
lbendlin
Super User
1 year ago
dufoq3

Me: Oh, I could do it this way or that way or maybe this other way?

Power Query, with a sigh: Sure, you could sow a button to your cheek, but why not try the obvious way?

Thank you!

Omid_Motamedise
Super User
1 year ago
This is not hard but I do not access to computer and I just say you the logic by my phone.
Add an index column starting from 0.
Then use number.integerdevide and devide each index by 2 so it result in 0 for the first two row, 1 for the next two rows, and so on. Use this column for grouping.

For more tips see this videom
https://youtu.be/tDfx8qSLKfc
- Einomi
  Helper V
  1 year ago
  Thanks Omid_Motamedise I have done some grouping to get 1 for the 2 first rows then 2 for the next 2 but after this I am blocked, I am sure it is not hard but I miss a samll part 🙂
  - Omid_Motamedise
    Super User
    1 year ago
    In the next step, from home tab pick table.group and choose column 1 in the top section and pick sum as operation in the bottom section over a column like column 4. Press ok will lead to error, but go to the formula bar, find List.sum and replace it by the below formula
    
    each Text.Combine(_," ")

shafiz_p

Super User

1 year ago

Hi Einomi All the solution provided by others are simply great.

I’d like to share a straightforward solution using a graphical interface. You can address this problem by employing two index columns, a modulo column, self-merging queries, and column merging. Since every two rows need to be merged, follow these steps:

Create two index columns—one starting from 0 and the other from 1.
Add a modulo column based on the 0-based index divided by 2. This column will help identify each row as either 0 or 1
Perform a self-merge query to align every second row with the first row.
Use this modulo column to filter out the second rows in each repetition.
Finally, merge every two columns.

Give this approach a try, and it should help you achieve your desired outcome!

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZbbbtswDIZfheh1i8mn2L5MXCDrmm7GXKAY0l6ojhYLcaROttBmTz/6mHhuTl6XCzNmFPHXJ5LSfH7xXdIshyDh8YoJmLElGJ9suLi8EDpN0YwnAVzV9pqlHAKpRc4UugyCH8PyPLv78nQ5b/5dGwyygChXjOX4dsdomsGjJsQcQcQXDK55lrAMf7KJ/5hXtpymiDrWz0wtuWAQ8l+aihxuZyGOMQmZNgKhiLCrMlTsKqS4pgWELKNxgh650HGe7Wo1wfHdvqO/gFZ7112aRudEUZ5hvIA+P9MlK1exVfi+uHIZhucVgw3T8I+y6+J6T0azlYFWalOjst0PQOXapO8YhipIFN3ghvY3ci+mbdzuyzFg/SV5llk/GznXOl5BJPULzKgqd243+Q8BK/6UQSf9wXbsvmMYp8+yLM0Ug9blMqUq5fHZ0M6s0IimdFGy8oz62UgqCUH0igNhJuMVZqKAW71k6RnYyvHF/Baxbb+1wyDdsTceUyHhC1OrzYHO1UVjmI7V2mNAgoSqWGPb42yfCp0lSso1FufLOQnUkG53xwDfH/Udw9iE2AtSDHMvxVKzatYpHIWDjYt0NAwk5FveVorMaS6rBX9cB7fAMkjfMZBWiNUmU4ZHTF1uTWI7zn+stjvKRbEoZ0Tq546gqcLyLxhg3fEN9oMbAd8ElwJJ6rg8ZZzTKDZhWmUEXOL1HYPZ1Ts8SbnIf1fHOYTj4PZ4uhmmbZLtl38/BFFNhEmzZu1xGMWJ4CgrBSssFFXP06EZhkVaOwxRddWaMPazmOikjMI7Qed+MrQOibtVobOMU9GtR6xEWFYVCSclU79vOZg8Vt8xDFW0lnWUtRR/6zpQgL7rtraYK6Q5KwbqN7YPWLuSd3WU59xYlCe92nO1O8ipk6o7Pd0aGX3HMFj3Wq3YBjtEliu65iefgAfaVUTF4pVjz31IOBKcKFaucA/AauwlPCj6sr3YCwxUNgHPqZ+N4B9szUQxLxalXp5xWn5lr3CTs3V15TJM023tmeie/gA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"null",null,Replacer.ReplaceValue,{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}),
    #"Added Index" = Table.AddIndexColumn(#"Replaced Value", "Index", 0, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type),
    #"Inserted Modulo" = Table.AddColumn(#"Added Index1", "Modulo", each Number.Mod([Index], 2), type number),
    #"Merged Queries" = Table.NestedJoin(#"Inserted Modulo", {"Index.1"}, #"Inserted Modulo", {"Index"}, "Merged", JoinKind.LeftOuter),
    #"Expanded Query1 (2)" = Table.ExpandTableColumn(#"Merged Queries", "Merged", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, {"Column1.1", "Column2.1", "Column3.1", "Column4.1", "Column5.1", "Column6.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Query1 (2)", each ([Modulo] = 0)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index", "Index.1", "Modulo"}),
    #"Merged Columns" = Table.CombineColumns(#"Removed Columns",{"Column1", "Column1.1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Column1.2"),
    #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Column3", "Column3.1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Column3.2"),
    #"Merged Columns2" = Table.CombineColumns(#"Merged Columns1",{"Column4", "Column4.1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Column4.2"),
    #"Merged Columns3" = Table.CombineColumns(#"Merged Columns2",{"Column5", "Column5.1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Column5.2"),
    #"Merged Columns4" = Table.CombineColumns(#"Merged Columns3",{"Column6", "Column6.1"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Column6.2"),
    #"Reordered Columns" = Table.ReorderColumns(#"Merged Columns4",{"Column1.2", "Column2", "Column3.2", "Column4.2", "Column5.2", "Column6.2"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Column2.1"})
in
    #"Removed Columns1"

Best Regards,
Shahariar Hafiz

Forum Discussion

Merge 2 Rows

14 Replies

Related content

Merging 2 rows

Merge 2 rows in one row

Merge row cells

Create rows from 2 tables

Merge 2 rows as a header