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
Omid_Motamedise's avatar
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's avatar
    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's avatar
      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(_," ")

    • lbendlin's avatar
      lbendlin
      Super User
      1 year ago

      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's avatar
        Einomi
        Helper V
        1 year ago

        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