Log In
Register
Home
Active Member
April 1, 2022
Offline
Hello,
My elderly father loves numbers and greyhound racing. Watching him use pen and paper to copy information from a website was painful. I thought I would have a go at automating it for him. I have some experience with excel but I am brand new to power query. After many attempts I was able to produce results but they are extremely slow, to the point where pen and paper was faster.
I can produce a table of a single dog's racing history in around 20 seconds. As soon as I created a list of all dogs running for the day, the time increased to several minutes per dog. This would take more than 24hrs to run each day.
- Create a table of URLs of the meetings for the day. I failed at being able to accomplish this with power query, however I was able to do it by downloading Power BI and using "Add table using examples" (getTodayFieldsURLsMcode.txt)
- Export table to a csv file (data.csv)
- Create a table of runners/greyhounds that are racing today (TodayFullFieldListMcode.txt)
- Create a table of all runners/greyhounds racing history (TestTableMcode.txt)
- I invoked two functions to loop through the tables above (fxTodayRunnerNamesMcode.txt, fxCombinedMcode.txt)
If anyone with greater experience and knowledge than me could guide me through my errors, I would greatly appreciate it.
See Answer
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Ron,
can you upload your PBI model?
Active Member
April 1, 2022
Offline
Hello Catalin,
Thank you for your reply.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Ron,
Checked your model, but looks like it does not have the codes you uploaded in the first post. It's hard to rebuild your entire logic, can you provide a functional model that does produce results, even if it's slow?
Active Member
April 1, 2022
Offline
Hi Catalin,
Thanks again for your time. I did it all through Excel/Power Query. The only part I couldn't do there I was able to achieve with Power BI.
The text files above are the Mcode I used in Excel to create the final table. I am unsure what else I can upload. Should I post a link to the website along with a sample table of what I want?
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Please upload the excel file with your queries.
Thank you
Active Member
April 1, 2022
Offline
Thank you for your patience Catalin.
Files attached as requested
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Ron,
When you do a query from web, Power Query is using Web.Page function that converts the text into html, the source step looks like this:
= Web.Page(Web.Contents("https://www.thegreyhoundrecorder.com.au/form-guides/"))
If you remove the Web.Page function, you will see a binary object in PQ Editor. Right click that binary and choose Text, not HTML.
You'll be able to see the content of the page, in plain text, just apply filters in that column to get what you need, see the below query :
let
Source = Table.FromColumns({Lines.FromBinary(Web.Contents("https://www.thegreyhoundrecorder.com.au/form-guides/"), null, null, 65001)}),
#"Filtered Rows1" = Table.SelectRows(Source, each (Text.Contains([Column1] ,"/form-guides/") and Text.Contains([Column1] ,"/fields/"))),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Filtered Rows1", {{"Column1", each Text.AfterDelimiter(_, "<td><a href="""), type text}}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Extracted Text After Delimiter", {{"Column1", each Text.BeforeDelimiter(_, """><button"), type text}})
in
#"Extracted Text Before Delimiter"
Unfortunately, can't see a way to speed up the queries.
Answers Post
Active Member
April 1, 2022
Offline
Dear Catalin,
Thank you very much for sharing your time and knowledge. You have taught me more than I knew before posting. I hope others also learn from your teachings. If I ever get this fully working, I will post an update.
Thank you to all Admin for a wonderful platform and community.
1 Guest(s)
