Park Run Data - Scotland

[GorD]

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

 

[citizenbh]

My solution with all the races:

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"

 

[sandy666]

Correct - that is the essence of the problem

maybe like this

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Title[/td][td=bgcolor:#70AD47]Pos[/td][td=bgcolor:#70AD47]parkrunner[/td][td=bgcolor:#70AD47]Time[/td][td=bgcolor:#70AD47]Age Cat[/td][td=bgcolor:#70AD47]Age Grade[/td][td=bgcolor:#70AD47]Gender[/td][td=bgcolor:#70AD47]Gender Pos[/td][td=bgcolor:#70AD47]Club[/td][td=bgcolor:#70AD47]Note[/td][td=bgcolor:#70AD47]Total Runs[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]St Andrews parkrun #359 - 15/06/2019[/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]Steven WATTERS[/td][td=bgcolor:#E2EFDA]17:23[/td][td=bgcolor:#E2EFDA]VM35-39[/td][td=bgcolor:#E2EFDA]

0.768​

[/td][td=bgcolor:#E2EFDA]M[/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]Stonehaven Running Club[/td][td=bgcolor:#E2EFDA]First Timer![/td][td=bgcolor:#E2EFDA]

12​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]St Andrews parkrun #359 - 15/06/2019[/td][td]

2​

[/td][td]Alistair MCDONALD[/td][td]17:34[/td][td]SM25-29[/td][td]

0.7343​

[/td][td]M[/td][td]

2​

[/td][td][/td][td]New PB![/td][td]

6​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]St Andrews parkrun #359 - 15/06/2019[/td][td=bgcolor:#E2EFDA]

3​

[/td][td=bgcolor:#E2EFDA]Ross MAXWELL[/td][td=bgcolor:#E2EFDA]17:53[/td][td=bgcolor:#E2EFDA]SM20-24[/td][td=bgcolor:#E2EFDA]

0.7213​

[/td][td=bgcolor:#E2EFDA]M[/td][td=bgcolor:#E2EFDA]

3​

[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA]New PB![/td][td=bgcolor:#E2EFDA]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]St Andrews parkrun #359 - 15/06/2019[/td][td]

4​

[/td][td]Fergus INGLEDEW[/td][td]18:25[/td][td]SM20-24[/td][td]

0.7005​

[/td][td]M[/td][td]

4​

[/td][td]Fife AC[/td][td]PB stays at 00:18:01[/td][td]

14​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]St Andrews parkrun #359 - 15/06/2019[/td][td=bgcolor:#E2EFDA]

5​

[/td][td=bgcolor:#E2EFDA]Charles HOWORTH[/td][td=bgcolor:#E2EFDA]18:31[/td][td=bgcolor:#E2EFDA]VM35-39[/td][td=bgcolor:#E2EFDA]

0.721​

[/td][td=bgcolor:#E2EFDA]M[/td][td=bgcolor:#E2EFDA]

5​

[/td][td=bgcolor:#E2EFDA]Stonehaven Running Club[/td][td=bgcolor:#E2EFDA]PB stays at 00:17:59[/td][td=bgcolor:#E2EFDA]

43​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]St Andrews parkrun #359 - 15/06/2019[/td][td]

6​

[/td][td]Jason SHARP[/td][td]18:54[/td][td]VM40-44[/td][td]

0.7222​

[/td][td]M[/td][td]

6​

[/td][td]Falkland Trail Runners[/td][td]New PB![/td][td]

26​

[/td][/tr]
[/table]

title is extracted from the web page source
the table imported with standard way

 

[citizenbh]

My solution has all the races from 1 to 359.
There is a table with a history of the race:

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

 

[GorD]

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

 

[sandy666]

I posted part of the table only with title column added

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

edit:
I tried enter url manually (with Tor browser) and saw 403 forbidden

 

[GorD]

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.

 

[sandy666]

Sandy, that now looks perfect

that's fine but I am not sure they will keep structure always the same

 

[sandy666]

P.S Looks like I am unable to edit my original post.

You've only 10 minutes to edit after post

 

[sandy666]

[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:

 

[GorD]

Sandy, I still not sure how you extracted the date?

See code now thanks a lot