Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Einomi
Helper V
Helper V

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.

 

Screenshot 2024-09-11 121644.png

 

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

 

Merge2Rows.png

 

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 

1 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

View solution in original post

14 REPLIES 14
shafiz_p
Solution Sage
Solution Sage

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
Super User
Super User

Hi @Einomi, another solution:

 

Output

dufoq3_0-1726061967931.png

 

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

 


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

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

Thanks @dufoq3 I will test your solution a bit later

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.


Note: Check this link to learn how to use my query.
Check this link if you don't know how to provide sample data.

Omid_Motamedise
Memorable Member
Memorable Member

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

 

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 🙂

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

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.

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

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"

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

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors