Forum Discussion

Einomi's avatar
Einomi
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. How...
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • lbendlin's avatar
    lbendlin
    1 year ago

    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
shafiz_p's avatar
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:

  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