Forum Discussion

Einomi's avatar
Einomi
Icon for Helper V rankHelper V
2 years 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
    2 years 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
AlienSx's avatar
AlienSx
Icon for Super User rankSuper User
2 years ago
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's avatar
lbendlin
Icon for Super User rankSuper User
2 years 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
  • Einomi's avatar
    Einomi
    Icon for Helper V rankHelper V
    2 years ago

    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