I’ve searched the web and the forum and can’t quite seem to find a use case similar to this one. I’m trying to build a Masters (golf tournament) leaderboard inside of Power BI by using their web feed. Here is the link to the feed: https://www.masters.com/en_US/scores/feeds/scores.json
I’ve tried using Get Data > Web and then pasting the link there. However, it ends up timing out.
What is the best way to get and parse this type of data into a table in Power BI?
Thanks!
My experience with this sort of thing is it’s dependent on finding the right website to scrape. I do a lot of sports web scraping myself and the key is finding a site without a lot of ads, video, pop-ups, etc. – things that interfere with the web scraping process. In this case I found a site www.augusta.com that met this requirement, and scraped quite nicely with some basic Power Query transformation. If you take the M code that I provide below and paste that into a blank query, it should produce the following table which you can dynamically update just by refreshing the report.
I hope this is helpful.
let
Source = Web.BrowserContents("https://www.augusta.com/masters/leaderboard"),
#"Extracted Table From Html" = Html.Table(Source, {{"Column1", "TABLE[id='LargeLeaderboard'] > * > TR > TD[colspan=""10""]:not([rowspan]):nth-child(1):nth-last-child(1), TABLE[id='LargeLeaderboard'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(5), TABLE[id='LargeLeaderboard'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10), TABLE[id='LargeLeaderboard'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10), TABLE[id='LargeLeaderboard'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(1)"}, {"Column2", "TABLE[id='LargeLeaderboard'] > * > TR > TD[colspan=""10""]:not([rowspan]):nth-child(1):nth-last-child(1), TABLE[id='LargeLeaderboard'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(4), TABLE[id='LargeLeaderboard'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9), TABLE[id='LargeLeaderboard'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9)"}, {"Column3", "TABLE[id='LargeLeaderboard'] > * > TR > TD[colspan=""10""]:not([rowspan]):nth-child(1):nth-last-child(1), TABLE[id='LargeLeaderboard'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(4) + TH[colspan=""3""]:not([rowspan]):nth-child(3):nth-last-child(3), TABLE[id='LargeLeaderboard'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8), TABLE[id='LargeLeaderboard'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8)"}, {"Column4", "TABLE[id='LargeLeaderboard'] > * > TR > TD[colspan=""10""]:not([rowspan]):nth-child(1):nth-last-child(1), TABLE[id='LargeLeaderboard'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(4) + TH[colspan=""3""]:not([rowspan]):nth-child(3):nth-last-child(3), TABLE[id='LargeLeaderboard'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(7), TABLE[id='LargeLeaderboard'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(7)"}, {"Column5", "TABLE[id='LargeLeaderboard'] > * > TR > TD[colspan=""10""]:not([rowspan]):nth-child(1):nth-last-child(1), TABLE[id='LargeLeaderboard'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(4) + TH[colspan=""3""]:not([rowspan]):nth-child(3):nth-last-child(3), TABLE[id='LargeLeaderboard'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(7) + TH:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(6), TABLE[id='LargeLeaderboard'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(6)"}, {"Column6", "TABLE[id='LargeLeaderboard'] > * > TR > TD[colspan=""10""]:not([rowspan]):nth-child(1):nth-last-child(1), TABLE[id='LargeLeaderboard'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(4) + TH[colspan=""3""]:not([rowspan]):nth-child(3):nth-last-child(3) + TH[colspan=""4""]:not([rowspan]):nth-child(4):nth-last-child(2), TABLE[id='LargeLeaderboard'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(7) + TH:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(5), TABLE[id='LargeLeaderboard'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(5)"}, {"Column7", "TABLE[id='LargeLeaderboard'] > * > TR > TD[colspan=""10""]:not([rowspan]):nth-child(1):nth-last-child(1), TABLE[id='LargeLeaderboard'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(4) + TH[colspan=""3""]:not([rowspan]):nth-child(3):nth-last-child(3) + TH[colspan=""4""]:not([rowspan]):nth-child(4):nth-last-child(2), TABLE[id='LargeLeaderboard'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(7) + TH:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(7):nth-last-child(4), TABLE[id='LargeLeaderboard'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(7):nth-last-child(4)"}, {"Column8", "TABLE[id='LargeLeaderboard'] > * > TR > TD[colspan=""10""]:not([rowspan]):nth-child(1):nth-last-child(1), TABLE[id='LargeLeaderboard'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(4) + TH[colspan=""3""]:not([rowspan]):nth-child(3):nth-last-child(3) + TH[colspan=""4""]:not([rowspan]):nth-child(4):nth-last-child(2), TABLE[id='LargeLeaderboard'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(7) + TH:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(7):nth-last-child(4) + TH:not([colspan]):not([rowspan]):nth-child(8):nth-last-child(3), TABLE[id='LargeLeaderboard'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(7):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(8):nth-last-child(3)"}, {"Column9", "TABLE[id='LargeLeaderboard'] > * > TR > TD[colspan=""10""]:not([rowspan]):nth-child(1):nth-last-child(1), TABLE[id='LargeLeaderboard'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(4) + TH[colspan=""3""]:not([rowspan]):nth-child(3):nth-last-child(3) + TH[colspan=""4""]:not([rowspan]):nth-child(4):nth-last-child(2), TABLE[id='LargeLeaderboard'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(7) + TH:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(7):nth-last-child(4) + TH:not([colspan]):not([rowspan]):nth-child(8):nth-last-child(3) + TH:not([colspan]):not([rowspan]):nth-child(9):nth-last-child(2), TABLE[id='LargeLeaderboard'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(7):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(8):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(9):nth-last-child(2)"}, {"Column10", "TABLE[id='LargeLeaderboard'] > * > TR > TD[colspan=""10""]:not([rowspan]):nth-child(1):nth-last-child(1), TABLE[id='LargeLeaderboard'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(4) + TH[colspan=""3""]:not([rowspan]):nth-child(3):nth-last-child(3) + TH[colspan=""4""]:not([rowspan]):nth-child(4):nth-last-child(2) + TH:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(1), TABLE[id='LargeLeaderboard'] > * > TR > TH:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TH:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TH:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8) + TH:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(7) + TH:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(6) + TH:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(5) + TH:not([colspan]):not([rowspan]):nth-child(7):nth-last-child(4) + TH:not([colspan]):not([rowspan]):nth-child(8):nth-last-child(3) + TH:not([colspan]):not([rowspan]):nth-child(9):nth-last-child(2) + TH:not([colspan]):not([rowspan]):nth-child(10):nth-last-child(1), TABLE[id='LargeLeaderboard'] > * > TR > TD:not([colspan]):not([rowspan]):nth-child(1):nth-last-child(10) + TD:not([colspan]):not([rowspan]):nth-child(2):nth-last-child(9) + TD:not([colspan]):not([rowspan]):nth-child(3):nth-last-child(8) + TD:not([colspan]):not([rowspan]):nth-child(4):nth-last-child(7) + TD:not([colspan]):not([rowspan]):nth-child(5):nth-last-child(6) + TD:not([colspan]):not([rowspan]):nth-child(6):nth-last-child(5) + TD:not([colspan]):not([rowspan]):nth-child(7):nth-last-child(4) + TD:not([colspan]):not([rowspan]):nth-child(8):nth-last-child(3) + TD:not([colspan]):not([rowspan]):nth-child(9):nth-last-child(2) + TD:not([colspan]):not([rowspan]):nth-child(10):nth-last-child(1)"}}, [RowSelector="TABLE[id='LargeLeaderboard'] > * > TR"]),
#"Changed Type" = Table.TransformColumnTypes(#"Extracted Table From Html",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}}),
#"Removed Top Rows" = Table.Skip(#"Changed Type",1),
#"Replaced Value" = Table.ReplaceValue(#"Removed Top Rows","SCORING TO PAR","TOTAL SCORING TO PAR",Replacer.ReplaceText,{"Column3"}),
#"Removed Blank Rows" = Table.SelectRows(#"Replaced Value", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
#"Promoted Headers" = Table.PromoteHeaders(#"Removed Blank Rows", [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"_1", type text}, {"TOTAL SCORING TO PAR", type text}, {"SCORING TO PAR", type text}, {"SCORING TO PAR_2", type text}, {"ROUNDS", type text}, {"ROUNDS_3", type text}, {"ROUNDS_4", type text}, {"ROUNDS_5", type text}, {"_6", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"SCORING TO PAR", "THRU"}, {"SCORING TO PAR_2", "TODAY"}, {"ROUNDS", "R1"}, {"ROUNDS_3", "R2"}, {"ROUNDS_4", "R3"}, {"ROUNDS_5", "R4"}, {"_6", "TOTAL"}}),
#"Removed Top Rows1" = Table.Skip(#"Renamed Columns",1),
#"Renamed Columns1" = Table.RenameColumns(#"Removed Top Rows1",{{"", "STANDING"}, {"_1", "NAME"}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns1", "NAME", Splitter.SplitTextByEachDelimiter({"*"}, QuoteStyle.Csv, false), {"NAME.1", "NAME.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"NAME.1", type text}, {"NAME.2", type text}}),
#"Merged Columns" = Table.CombineColumns(#"Changed Type2",{"NAME.1", "NAME.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
#"Renamed Columns2" = Table.RenameColumns(#"Merged Columns",{{"Merged", "NAME"}}),
#"Trimmed Text" = Table.TransformColumns(#"Renamed Columns2",{{"NAME", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"NAME", Text.Clean, type text}})
in
#"Cleaned Text"
Hi @ChrisHervochon, a response to this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!