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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
mhgottsc
Helper I
Helper I

Combining every 2 rows

I am attempting to combine every 2 rows together so: rows 1 and 2, rows 3 and 4, rows 5 and 6 and so on.

How would I do this in power query?

 

 

1 ACCEPTED SOLUTION

Hi @mhgottsc 

 

Download sample PBIX file with the following code

 

This query does what you want.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZLLTsMwEEV/ZRQ2IFVg59HHEtIiELQUWHQRdeEmQ7Hk2MEPVeHrcaIuQEWpJVuW7PG5M3emKCISjSJyTbrjkoBqUJobAqVQBqsrf3nH97BS3LSwVFZpE21HRSSdEP7tePj10jRKWye5beECaJykfdw5+FLtuEDIVd0w2YagPXkShH5m5lsoeJSVM1ZzDEs8nWR9HO24cTLNBhTypxm8Y+m0/xsCTyg9dYX24L9c3RrLBOQCmYYNs6hh7VU+OAbaT9JfVQyZ9Habvwa5TrJT1/9Jfa4O0voNK77/tKVwuyBj4nTax407bjc8QyljBQ9+DhEWXw671jIZIjIjNA4anLlGVh+UFhUspPfeMi5rlDZEZJwcK0nOqWyYrFD7hsJ9HkJO09iTtz8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t, Expected = _t, #"Next Activity" = _t, #"Company / Opportunity" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}, {"Expected", Int64.Type}, {"Next Activity", type text}, {"Company / Opportunity", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if Number.Mod([Index],2) = 0 then try Text.Combine({[#"Company / Opportunity"], #"Added Index"[#"Company / Opportunity"]{[Index]+1}}, "," ) otherwise "" else ""),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Value] <> null))
in
    #"Filtered Rows"

mergerows.png

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

4 REPLIES 4
PaulDBrown
Community Champion
Community Champion

You could create a duplicate of the table. In table 1, add an index column starting at 1; in Table 2, add an index column starting at 0. Then merge both table based on the index columns





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






PhilipTreacy
Super User
Super User

Hi @mhgottsc 

 

Download example PBIX file

 

You could transpose your table then merge pairs of columns, then transpose the table again (back to rows).

 

trans1.png

trans2.png

trans3.png

trans4.png

 

But without seeing your data I don't know if that is a viable solution for you.  You might have too many rows to do this by hand initially.  Please supply some sample data or preferably your PBIX file.

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Here is a sceen shot of the data if that helps:

For the company opportunity column I am wanting to have data in row 2 appended on to row 1, with the same pattern continuing for row 4 being appended to row 3, but just for the company/ opportunity column so that I can go through and delete the even rows that have the null value. Thanks so much!

mhgottsc_0-1636469570122.png

 

Hi @mhgottsc 

 

Download sample PBIX file with the following code

 

This query does what you want.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZLLTsMwEEV/ZRQ2IFVg59HHEtIiELQUWHQRdeEmQ7Hk2MEPVeHrcaIuQEWpJVuW7PG5M3emKCISjSJyTbrjkoBqUJobAqVQBqsrf3nH97BS3LSwVFZpE21HRSSdEP7tePj10jRKWye5beECaJykfdw5+FLtuEDIVd0w2YagPXkShH5m5lsoeJSVM1ZzDEs8nWR9HO24cTLNBhTypxm8Y+m0/xsCTyg9dYX24L9c3RrLBOQCmYYNs6hh7VU+OAbaT9JfVQyZ9Habvwa5TrJT1/9Jfa4O0voNK77/tKVwuyBj4nTax407bjc8QyljBQ9+DhEWXw671jIZIjIjNA4anLlGVh+UFhUspPfeMi5rlDZEZJwcK0nOqWyYrFD7hsJ9HkJO09iTtz8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Value = _t, Expected = _t, #"Next Activity" = _t, #"Company / Opportunity" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Value", Int64.Type}, {"Expected", Int64.Type}, {"Next Activity", type text}, {"Company / Opportunity", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if Number.Mod([Index],2) = 0 then try Text.Combine({[#"Company / Opportunity"], #"Added Index"[#"Company / Opportunity"]{[Index]+1}}, "," ) otherwise "" else ""),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Value] <> null))
in
    #"Filtered Rows"

mergerows.png

 

Regards

 

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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