cancel
Showing results for 
Search instead for 
Did you mean: 
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
Super User
Super User

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
Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

Ted's Dev Camp - October 6, 2022

Watch the playback of Session 26 with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Ignite 2022

What's Next at Microsoft Ignite 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Top Solution Authors