Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 22

Thread: Park Run Data - Scotland
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    Board Regular
    Join Date
    Sep 2013
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Park Run Data - Scotland

    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 by citizenbh; Jun 15th, 2019 at 02:18 PM.

  2. #12
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,334
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Park Run Data - Scotland

    Quote Originally Posted by GorD View Post
    Correct - that is the essence of the problem
    maybe like this

    Title Pos parkrunner Time Age Cat Age Grade Gender Gender Pos Club Note Total Runs
    St Andrews parkrun #359 - 15/06/2019
    1
    Steven WATTERS 17:23 VM35-39
    0.768
    M
    1
    Stonehaven Running Club First Timer!
    12
    St Andrews parkrun #359 - 15/06/2019
    2
    Alistair MCDONALD 17:34 SM25-29
    0.7343
    M
    2
    New PB!
    6
    St Andrews parkrun #359 - 15/06/2019
    3
    Ross MAXWELL 17:53 SM20-24
    0.7213
    M
    3
    New PB!
    2
    St Andrews parkrun #359 - 15/06/2019
    4
    Fergus INGLEDEW 18:25 SM20-24
    0.7005
    M
    4
    Fife AC PB stays at 00:18:01
    14
    St Andrews parkrun #359 - 15/06/2019
    5
    Charles HOWORTH 18:31 VM35-39
    0.721
    M
    5
    Stonehaven Running Club PB stays at 00:17:59
    43
    St Andrews parkrun #359 - 15/06/2019
    6
    Jason SHARP 18:54 VM40-44
    0.7222
    M
    6
    Falkland Trail Runners New PB!
    26


    title is extracted from the web page source
    the table imported with standard way
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  3. #13
    Board Regular
    Join Date
    Sep 2013
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Park Run Data - Scotland

    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.../eventhistory/

  4. #14
    Board Regular
    Join Date
    Jan 2004
    Location
    Fife, Scotland
    Posts
    1,445
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Park Run Data - Scotland

    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

  5. #15
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,334
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Park Run Data - Scotland

    I posted part of the table only with title column added

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

    edit:
    I tried enter url manually (with Tor browser) and saw 403 forbidden
    Last edited by sandy666; Jun 15th, 2019 at 02:45 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  6. #16
    Board Regular
    Join Date
    Jan 2004
    Location
    Fife, Scotland
    Posts
    1,445
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Park Run Data - Scotland

    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.

  7. #17
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,334
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Park Run Data - Scotland

    Quote Originally Posted by GorD View Post
    Sandy, that now looks perfect
    that's fine but I am not sure they will keep structure always the same
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  8. #18
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,334
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Park Run Data - Scotland

    Quote Originally Posted by GorD View Post
    P.S Looks like I am unable to edit my original post.
    You've only 10 minutes to edit after post
    Last edited by sandy666; Jun 15th, 2019 at 02:47 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  9. #19
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,334
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Park Run Data - Scotland

    Code:
    // 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
    and additional surprise:

    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  10. #20
    Board Regular
    Join Date
    Jan 2004
    Location
    Fife, Scotland
    Posts
    1,445
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Park Run Data - Scotland

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

    See code now thanks a lot
    Last edited by GorD; Jun 15th, 2019 at 03:00 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •