Park Run Data - Scotland

citizenbh

Board Regular
Joined
Sep 19, 2013
Messages
137
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:

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,438
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
 

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
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
 

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,438
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:

 

GorD

Well-known Member
Joined
Jan 17, 2004
Messages
1,446
Sandy, I still not sure how you extracted the date?

See code now thanks a lot
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,753
Messages
5,470,571
Members
406,707
Latest member
drkjz

This Week's Hot Topics

Top