Merge 2 Rows

[Einomi]

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 

[shafiz_p]

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:

1. Create two index columns—one starting from 0 and the other from 1.
2. 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
3. Perform a self-merge query to align every second row with the first row.
4. Use this modulo column to filter out the second rows in each repetition.
5. 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

[dufoq3]

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

[lbendlin]

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

[Einomi]

Thanks @dufoq3 I will test your solution a bit later

[dufoq3]

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.

[Omid_Motamedise]

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]

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]

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(_," ")

[lbendlin]

Please provide sample data that fully covers your issue in a usable format (NOT a screenshot).
Please show the expected outcome based on the sample data you provided.

[Einomi]

The challenge table

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]),
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}})
in
    #"Type modifié"

The expected outcome

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lY/NasMwEIRfZfA5IZLitvbRcSCHpCCao+ODbC+2iJGJft4/m9IeQgule1n4mJ2daZrsYzEhop5sfyWHE42QmxzZKnNpnnlVuxprsGrAOXqi+MX2NFvUS3KRPN7JzAGXJIR6xdkOhL0NEwXWSsEjt0WRIxflJf6G2lXz6VmljvxoHUHbWzIu4njSfKCEOPwVRXtaa8MlBmgKpp+YLEPq41MIhZfy7Sf4DrDzxgZ2qE3XmZFYmP/v9XN3JWRRPEykkmXWtnc=", 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]),
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}})
in
    #"Type modifié"

Thanks

[lbendlin]

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 Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.RoundDown([Index]/2),Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Custom", {"Custom", "Index"}, "Attribute", "Value"),
    #"Replaced Value1" = Table.ReplaceValue(#"Unpivoted Other Columns",each [Value],(k)=> k[Value] & " " & Table.SelectRows(#"Unpivoted Other Columns", each [Custom]=k[Custom] and [Attribute]=k[Attribute] and[Index]=k[Index]+1 )[Value]{0},Replacer.ReplaceValue,{"Value"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value1", each Number.IsEven([Index])),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Attribute]), "Attribute", "Value"),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns",{"Custom"})
in
    #"Removed Columns1"

[AlienSx]

let
    lst = List.Split(Table.ToRows(challenge_table), 2),
    trn = List.Transform(lst, (x) => ((w) => List.Transform(w, (z) => Text.Combine(z, " ")))(List.Zip(x))),
    tbl = Table.FromRows(trn, Value.Type(challenge_table))
in
    tbl

[lbendlin · Accepted solution]

@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

[Einomi]

@AlienSx Thanks so much, so powerful. However, in the Column5 and Column6 for some rows I get an error because PQ cannot convert the value to text

@lbendlin I did not check you first suggestion, rather the addition on @AlienSx and with your addition I do not get any error on any rows, so it is good 🙂

Thanks both of you for the prompt help