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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,456
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,456
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,100,064
Messages
5,472,257
Members
406,809
Latest member
haf19

This Week's Hot Topics

Top