Park Run Data - Scotland

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,447
I have created a query to pull all the park run data from all the scottish park runs which works ok

it uses 43ish urls in a table (a few tables as website didn't like all together) like below

https://www.parkrun.org.uk/standrews/results/latestresults/


I get all the data coming through fine , but the table of data doesn't contain a date - which is visible on the webpage above the data table.

My concern is that if a particular park run didn't happen one week i would not know and pull the data through as this weeks

So how can i load the data from the above url and include the date

Thanks for any help
 
My solution with all the races:
Code:
let
     Source = Web.Page(Web.Contents("https://www.parkrun.org.uk/standrews/results/eventhistory/")),
     Data0 = Source{0}[Data],
     #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Run", Int64.Type}, {"Date", type text}, {"Number Runners", Int64.Type}, {"Number Volunteers", Int64.Type}, {"", type text}, {"Male First Finishers parkrunner", type text}, {"Male First Finishers Time", type time}, {"2", type text}, {"Female First Finishers parkrunner", type text}, {"Female First Finishers Time", type time}}),
     #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Number Runners", "Number Volunteers", "", "Male First Finishers parkrunner", "Male First Finishers Time", "2", "Female First Finishers parkrunner", "Female First Finishers Time"}),
     #"Changed Type with Locale" = Table.TransformColumnTypes(#"Removed Columns", {{"Date", type date}}, "en-GB"),
     #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Run", type text}}),
     #"Added Custom" = Table.AddColumn(#"Changed Type1", "All_Results", each Web.Page(Web.Contents("https://www.parkrun.org.uk/standrews/results/weeklyresults/?runSeqNumber=" & [Run]))),
     #"Expanded All_Results" = Table.ExpandTableColumn(#"Added Custom", "All_Results", {"Data"}, {"Data"}),
     #"Expanded Data" = Table.ExpandTableColumn(#"Expanded All_Results", "Data", {"Pos", "parkrunner", "Time", "Age Cat", "Age Grade", "Gender Pos", "Club", "Note", "Total Runs", "Total Runs2"}, {"Pos", "parkrunner", "Time", "Age Cat", "Age Grade", "Gender Pos", "Club", "Note", "Total Runs", "Total Runs2"})
 
in     

   #"Expanded Data"
 
Last edited:
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Correct - that is the essence of the problem

maybe like this

TitlePosparkrunnerTimeAge CatAge GradeGenderGender PosClubNoteTotal Runs
St Andrews parkrun #359 - 15/06/2019
1​
Steven WATTERS17:23VM35-39
0.768​
M
1​
Stonehaven Running ClubFirst Timer!
12​
St Andrews parkrun #359 - 15/06/2019
2​
Alistair MCDONALD17:34SM25-29
0.7343​
M
2​
New PB!
6​
St Andrews parkrun #359 - 15/06/2019
3​
Ross MAXWELL17:53SM20-24
0.7213​
M
3​
New PB!
2​
St Andrews parkrun #359 - 15/06/2019
4​
Fergus INGLEDEW18:25SM20-24
0.7005​
M
4​
Fife ACPB stays at 00:18:01
14​
St Andrews parkrun #359 - 15/06/2019
5​
Charles HOWORTH18:31VM35-39
0.721​
M
5​
Stonehaven Running ClubPB stays at 00:17:59
43​
St Andrews parkrun #359 - 15/06/2019
6​
Jason SHARP18:54VM40-44
0.7222​
M
6​
Falkland Trail RunnersNew PB!
26​

title is extracted from the web page source
the table imported with standard way
 
Upvote 0
Sandy, that now looks perfect

Peter - I'm slightly worried too that we are causing them issues and they think they are under attack lol - I think illl remove the url from thr original post in cae people all over the world are running function queries
 
Upvote 0
Thanks Sandy

Citizenbh - sorry i missed your post somehow - ill check that out too

P.S Looks like I am unable to edit my original post.
 
Upvote 0
Code:
[SIZE=1]// Table02
let
    Source = Web.Page(Web.Contents("https://www.parkrun.org.uk/standrews/results/latestresults/")),
    Data = Source{0}[Data],
    Type = Table.TransformColumnTypes(Data,{{"Pos", Int64.Type}, {"parkrunner", type text}, {"Time", type text}, {"Age Cat", type text}, {"Age Grade", Percentage.Type}, {"", type text}, {"Gender Pos", Int64.Type}, {"Club", type text}, {"Note", type text}, {"Total Runs", Int64.Type}, {"Total Runs2", type text}})
in
    Type

// Table0
let
    Source = Table.FromColumns({Lines.FromBinary(Web.Contents("https://www.parkrun.org.uk/standrews/results/latestresults/"))}),
    RemoveTopRows = Table.Skip(Source,217),
    KeeptFirstRow = Table.FirstN(RemoveTopRows,3),
    Trim = Table.TransformColumns(KeeptFirstRow,{{"Column1", Text.Trim, type text}}),
    Clean = Table.TransformColumns(Trim,{{"Column1", Text.Clean, type text}}),
    Transpose = Table.Transpose(Clean),
    MergeOne = Table.CombineColumns(Transpose,{"Column1", "Column2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    Extract = Table.TransformColumns(MergeOne, {{"Column3", each Text.Start(_, 10), type text}}),
    MergeTwo = Table.CombineColumns(Extract,{"Merged", "Column3"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Title")
in
    MergeTwo

// Result
let
    Source = Table.NestedJoin(Table02,{"parkrunner"},Table0,{"Title"},"Table0",JoinKind.FullOuter),
    Expand = Table.ExpandTableColumn(Source, "Table0", {"Title"}, {"Title"}),
    FillUp = Table.FillUp(Expand,{"Title"}),
    Reorder = Table.ReorderColumns(FillUp,{"Title", "Pos", "parkrunner", "Time", "Age Cat", "Age Grade", "", "Gender Pos", "Club", "Note", "Total Runs", "Total Runs2"}),
    RemBottomRow = Table.RemoveLastN(Reorder,1),
    Ren = Table.RenameColumns(RemBottomRow,{{"", "Gender"}}),
    ROC = Table.SelectColumns(Ren,{"Title", "Pos", "parkrunner", "Time", "Age Cat", "Age Grade", "Gender", "Gender Pos", "Club", "Note", "Total Runs"})
in
    ROC[/SIZE]

and additional surprise:

screenshot-77.png
 
Upvote 0
Sandy, I still not sure how you extracted the date?

See code now thanks a lot
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top