Power Query VBA to Omit/Delete & Seperate Data From .CSV

[Ourkid123uk]

Hi Guys!

I have a .CSV file which contains listings of horse races for a day which i have imported into Power Query and removed some columns of data that i dont need.

Im only interested in The Horse Races that have 8,9,10,11 or 12 Runners. Because these files contain all the races for that day and no reference of how many runners for each race, how can i omit or delete all the other races and just leave 8,9,10,11 or 12 runner races?

I would like to then load these into the worksheet but add a space between each race.


The only way i could think is to add some VBA code within Power Query that once in race order counts how many Rows have the same start time and there for calculates how many runners there were. How can i manage this data?

I appreciate any suggestions or help you may have, thanks for looking!


I have included a copy of my workbook as i think its necessary.

[rorya]

You could create another query that groups by cols A and B and uses Countrows to determine the number of runners. Then filter that accordingly. You can then merge that query with your existing one so that only the matching rows on col A and B are included in the results.

[Ourkid123uk]

That sounds like it would work, thanks rorya!

Im just not sure how to implement what you suggested. Would i load the CSV file into the worksheet then do what you said or could i do this somehow in Power Query using VBA?

[Ourkid123uk]

Right! i have done the first part which is great! How do i merge these back together, any idea?

This i what i have..

[rorya]

There's a merge Queries button on the toolbar in PQ.

[Ourkid123uk]

Ive clicked that but now im abit lost as to how i should be merging them

I cant quite get my head round it

[rorya]

This details the steps: https://www.powerquery.training/portfolio/merge-tables/

Essentially you need to select the columns to join the queries on. I can't do it for you as I don't have your csv file and I have an older version of PQ.

[Ourkid123uk]

Aha!

Thank you i have done that


If i could just pick you brains one more time. I have the desired data in descending order now. Is there anyway to add a space in the rows between the different races? Basically by saying after all the rows with the same date and time then add a space before the next row

[rorya]

It might be possible but I confess I don't know how. It's kind of contrary to the purpose of PQ. You could load the data into a pivot table and add a blank row that way.

[CK76]

For any data, I'd not recommend mixing in blanks. It's one of the worst thing you can do to data structure (considering subsequent analysis, charting etc).

I'd recommend just using CF. Assuming [EVENT_DT] is in Column B.
Select entire table's data body range.
Use following formula in CF and apply bottom border format.
=($B2<>$B3)

Sample Result:
0.JPG

[Ourkid123uk]

Brilliant!


Thats working guys thanks for your help and feedback left!

[CK76]

Glad that it helped and thanks for the rep

[rorya]

Ta much.