The Power of Power Query

[paspuggie48]

BA Admin and Fellow Members

As you will have seen Memphis has been posting a spreadsheet solution using Power Query (PQ).

I'd like to say, with a smile, that I was the one that mentored him and introduced him to that way of working and he has gladly taken it in his stride and started using it in anger.

Trust me when I say I use this PQ daily at work, manipulating millions of rows of data and calcs, all without VBA coding. It truly revolutionises the way one works in Excel.

So, my question is directed more-so to the Admin staff, can we have a 'sticky' for not just Excel/Spreadsheet solutions but Power Query only????

Let me give some examples: -

Have you ever wondered what Trap wins the most for each Greyhound course and can be updated at the click of one button?

From the GreyhoundStats webpages you can use PQ to collate that information for the past 9 years from multiple URLs and tabulate it all into 1 Table. To refresh this Table every morning takes about 10 seconds.

Screenshot 2020-10-29 113707.png

Have you ever wondered what Fav wins in each Trap for each Greyhound course? From the GreyhoundStats webpages you can use PQ to collate that information for the past 9 years from multiple URLs and tabulate it all into 1 Table. To refresh this every morning takes about 10 seconds.

Screenshot 2020-10-29 113644.png

Did you know you can collate the BF Historic records, count the amount of races each horse has ran and find those that have raced 9 times or more this year AND if they are running today? To do this I used NigelK's fantastic download tool to get the BF History csv files (I converted it so I can do this daily). Then I used PQ to connect to all the 300+ CSV files and created a very simple Query & Function to connect to Timeform's list of runner's today and combined both Query's to create the following table. It takes around 30 seconds to get this information dynamically, each and ever day.

Screenshot 2020-10-29 113748.png

The great thing with PQ is there is no coding and one can do any calcs/formulas thereafter to add to one's Tables.

[paspuggie48]

Ever wanted Jockey Stats each day, at the click of one button?

Screenshot 2020-10-29 115646.png

[paspuggie48]

Steamers & Drifters from ATR.

You can set the Refresh rate for every minute or hour or whatever.

Screenshot 2020-10-29 120629.png

[MemphisFlash]

+1
nice post.
could i have a copy of your modifed nigel spreadsheet please.

[paspuggie48]

How one can use PQ to connect to multiple daily P&L csv files from BF and combine them into weekly figures and create a running profit.

One click again to refresh each day, simply dump your csv in a folder and let PQ do the rest. Takes seconds to update.

Screenshot 2020-10-29 121235.png

[paspuggie48]

nice post.
could i have a copy of your modifed nigel spreadsheet please.

Sent to you student Memphis

[paspuggie48]

For you DOBBERS out there.

Which horse has ran more than twice this year and it's In-Play minimum has averaged more than 50% for all it's race and is running today. Here is just one example >>>

Screenshot 2020-10-29 122526.png

[murdok]

if you can send me the spreadsheet I also appreciate it

thx

[paspuggie48]

Combine Football stats.

Simply dump the files into 1 folder and get PQ to combine them all into 1 table. Here I have combined (Appended) each and every year for each and every league.

Took me ten minutes to do.

Screenshot 2020-10-29 123416.png

[paspuggie48]

if you can send me the spreadsheet I also appreciate it

thx

DM me your email murdock.

Although I modified it for daily use, ALL the credit goes to NigelK though !!!

[Cardano]

Could you please send me a copy of the spreadsheet.

[paspuggie48]

Could you please send me a copy of the spreadsheet.

Might need to do some screenshots and instructions as I use this downloader daily. I have modified it for my purposes and will have to explain how/why.

DM your email cardano

[murdok]

already sent my email by mp

and thx

[paspuggie48]

already sent my email by mp

and thx

To be honest, it may be easier if you download NigelK's original file as I have doctored my copy to suit me

viewtopic.php?f=54&t=14395

I'll work on my version and get some instructions drafted as it differs quite a lot

[paspuggie48]

Could you please send me a copy of the spreadsheet.

As above Cardano