Power Query - Example Spreadsheets (Indexed by Sport)

Post Reply
User avatar
paspuggie48
Posts: 611
Joined: Thu Jun 20, 2013 9:22 am
Location: South-West

jamesg46 wrote:
Thu Dec 03, 2020 6:48 pm
Cant imagine you would have much if any of an issue anyway, pinging their website a few times a day (assuming none of the files need constant refreshes) with all different i.p address's that have downloaded the file isn't going to raise many eyebrows. Defo a good idea to scrap the logo though.
I don't even use it, it was just an example of what can be done. I could have quite easily (and have done) copy 'n pasted the data into Excel by hand and as one knows it only takes seconds. The beauty of what Memphis and I are doing is automating it and yes I agree it's not pinging their website more than once a day, if that :)

To save face, not just to me but to BA, is to remove logos and delete the original post...and post a revised edition.
jamesg46
Posts: 3769
Joined: Sat Jul 30, 2016 1:05 pm

paspuggie48 wrote:
Thu Dec 03, 2020 6:53 pm
jamesg46 wrote:
Thu Dec 03, 2020 6:48 pm
Cant imagine you would have much if any of an issue anyway, pinging their website a few times a day (assuming none of the files need constant refreshes) with all different i.p address's that have downloaded the file isn't going to raise many eyebrows. Defo a good idea to scrap the logo though.
I don't even use it, it was just an example of what can be done. I could have quite easily (and have done) copy 'n pasted the data into Excel by hand and as one knows it only takes seconds. The beauty of what Memphis and I are doing is automating it and yes I agree it's not pinging their website more than once a day, if that :)

To save face, not just to me but to BA, is to remove logos and delete the original post...and post a revised edition.
I agree, I'm a scraping fan myself. I've not downloaded any but from what I've seen from the screen grabs (especially a Greyhound sheet Memphis has done) its all looks nice work, way better than mine.
User avatar
paspuggie48
Posts: 611
Joined: Thu Jun 20, 2013 9:22 am
Location: South-West

I think we are all scraping at some point and it brings into the whole factor (apart from advertising the logo in plain sight) if any solution on this Forum, the plethora of other Forums around, the tonne of macros people who have and will continue to develop and the Parsehub software packages of the world, are all susceptible or being subjected to website police monitoring.

Personally, the sheets I use, I get & transform data once (because I only need to scrape once), I have to assume and have some doubt that doesn't compare to some Python-Machine Learning-AI-Geeky programmes out there that are pinging every nano-second ;)
User avatar
paspuggie48
Posts: 611
Joined: Thu Jun 20, 2013 9:22 am
Location: South-West

P.S. I'm not really a scraper...I just wanted to see if I could do it LOL.

My bag is lots of data (e.g. BF Historic files) and connecting and transforming hundreds if not thousands of data files.

My record thus far was where I converted 10,300 A4 sized PDF documents into text files, each containing thousands of pages. This totalled over 60 million lines of sentences/words. With PQ I was able to connect to all 10,300 txt files and "find' information that has never been available or known of before in the history of our organisation. Of course it hasn't, I mean which human being is going to go through millions of pages to find something? It took me minutes to search for stuff !

Or where I was able to find data 'within' 20,000 rows of data in a column where a number plate was hidden in a sentence of words and then compare it to another column in a different workbook which had 30,000 rows of data and pull out the adjacent column of information...and repeat that same process for another column. That's 20,000*30,000*2 = 1.2 Billion calcs ! It would have literally took a person a year to do that task .My laptop struggled but did it in 4 hours LOL.

That's the Power of Power Query ;)
jamesg46
Posts: 3769
Joined: Sat Jul 30, 2016 1:05 pm

paspuggie48 wrote:
Thu Dec 03, 2020 7:44 pm
P.S. I'm not really a scraper...I just wanted to see if I could do it LOL.

My bag is lots of data (e.g. BF Historic files) and connecting and transforming hundreds if not thousands of data files.

My record thus far was where I converted 10,300 A4 sized PDF documents into text files, each containing thousands of pages. This totalled over 60 million lines of sentences/words. With PQ I was able to connect to all 10,300 txt files and "find' information that has never been available or known of before in the history of our organisation. Of course it hasn't, I mean which human being is going to go through millions of pages to find something? It took me minutes to search for stuff !

Or where I was able to find data 'within' 20,000 rows of data in a column where a number plate was hidden in a sentence of words and then compare it to another column in a different workbook which had 30,000 rows of data and pull out the adjacent column of information...and repeat that same process for another column. That's 20,000*30,000*2 = 1.2 Billion calcs ! It would have literally took a person a year to do that task .My laptop struggled but did it in 4 hours LOL.

That's the Power of Power Query ;)
Seems Memphis did a good job of teaching you ;)
User avatar
paspuggie48
Posts: 611
Joined: Thu Jun 20, 2013 9:22 am
Location: South-West

LMAO!!!!
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

Memphis is a legend
User avatar
paspuggie48
Posts: 611
Joined: Thu Jun 20, 2013 9:22 am
Location: South-West

Are you up for a challenge Memphis? This is right up your street to scrape data from a website.

I had a message and a request from Jasonffc1, basically; he wants to have the Trap data for all the Australian Greyhound courses.

The only link he gave me was thegreyhoundrecorder.com.au/tracks/bathurst. Unfortunately he gave me no more information except that link.

Obviously there are more URLs for each course but I'm sure with your skills you could do the other courses too and combine all the records for each year in one Table?
User avatar
MemphisFlash
Posts: 2126
Joined: Fri May 16, 2014 10:12 pm
Location: Leicester

not sure i'd be able to do it, as you have to go back and forth on the website, but i'd be interested to see what you produce, as if you set the challenge you already know how to do it.
User avatar
paspuggie48
Posts: 611
Joined: Thu Jun 20, 2013 9:22 am
Location: South-West

MemphisFlash wrote:
Sat Dec 05, 2020 9:51 am
not sure i'd be able to do it, as you have to go back and forth on the website, but i'd be interested to see what you produce, as if you set the challenge you already know how to do it.
Oh, thought you might know, I mean everyone thinks you are the master <teehee>

Hint: the solution is exactly how you have done it before bud. Create a 'function' for the list of URLs Tom :) ;)
jamesg46
Posts: 3769
Joined: Sat Jul 30, 2016 1:05 pm

That wasn't weird at all <teehee> Hey Memphis let me prove I'm better than you in the forum.

Can you just stop... we get it, we know that Memphis wouldn't be great at what he does if it weren't for you.... Jesus flipping christ you're the "master" and the teacher... the best there has ever been and ever will be, the number plate finder in some sequence of sentences. Great job.
User avatar
paspuggie48
Posts: 611
Joined: Thu Jun 20, 2013 9:22 am
Location: South-West

Yes James, it's called banter bud..no more proving lessons to perform :D :) ;)
User avatar
paspuggie48
Posts: 611
Joined: Thu Jun 20, 2013 9:22 am
Location: South-West

Could have sworn I'd already posted a message about this but can't see it....

So, just to say I've asked Dallas to edit the PQ posts inline with pertinent and apt comments about copyright / data infringements / logos etc.

Most are renamed and back to basics but of course have the same PQ capability, should anyone wish to use them.
jamesg46
Posts: 3769
Joined: Sat Jul 30, 2016 1:05 pm

paspuggie48 wrote:
Sat Dec 05, 2020 11:13 am
Yes James, it's called banter bud..no more proving lessons to perform :D :) ;)
If you think "proving lessons" is banter then you're a lucky man that Memphis is still your friend, especially since you seem to want him to prove it infront of others.
User avatar
paspuggie48
Posts: 611
Joined: Thu Jun 20, 2013 9:22 am
Location: South-West

jamesg46 wrote:
Sat Dec 05, 2020 11:43 am
paspuggie48 wrote:
Sat Dec 05, 2020 11:13 am
Yes James, it's called banter bud..no more proving lessons to perform :D :) ;)
If you think "proving lessons" is banter then you're a lucky man that Memphis is still your friend, especially since you seem to want him to prove it infront of others.
Yeah he emails now and then. After he got the bug he's a man on a mission and that's great, he really enjoys it...he's done very well in the last month or so and it's easier if one enjoys doing something they like.
Post Reply

Return to “Excel Power Query”