Importing web data with Power Query

[Chippy68]

Hi Guys,

I have built a pretty cool fully automated golf pool spreadsheet and discovered that I can bring in the Live Scoring from the web which would COMPLETELY automate the process (and look pretty fancy in the process). The problem is I've never used Power Query, but I have the M-Code that I need to use to perform this function. I have Office 365 ProPlus. Any chance anyone can walk me through getting this into Power Query and then into my existing workbook. i would also just like to have the players Full Name and Total Score, I don't need round by round scoring. Obviously looking to have this setup before the Masters which starts next week.

Appreciate any help I can get.

Here is the code:

PowerQuery works with the Golf Channel's website. This is the M-code for PQ.

let
Source = Web.Page(Web.Contents("http://www.golfchannel.com/tours/pga-tour/2017/masters-tournament/")),
Data0 = Source{0}[Data],
#"Changed Type" = Table.TransformColumnTypes(Data0,{{"", type text}, {"POS", type text}, {"MOV", type text}, {"PLAYER", type text}, {"OVERALL", type text}, {"THRU", type text}, {"RND", type text}, {"R1", type text}, {"R2", type text}, {"R3", type text}, {"R4", type text}, {"TOT", type text}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"", "MOV"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([POS] <> "typeof(mps)=='object' && typeof(mps.getAd)=='function' && document.write(mps.getAd('midbanner1'));#(cr)#(lf)mps._execAd(""midbanner1"");" and [POS] <> "typeof(mps)=='object' && typeof(mps.getAd)=='function' && document.write(mps.getAd('midbanner2'));#(cr)#(lf)mps._execAd(""midbanner2"");")),
#"Replaced Value" = Table.ReplaceValue(#"Filtered Rows",null,"",Replacer.ReplaceValue,{"R1"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",null,"",Replacer.ReplaceValue,{"R2", "R3", "R4", "TOT"})
in
#"Replaced Value1"

Happy Easter

 

[citizenbh]

Replace in second row:

Data0 = Source{0}[Data]

with

Data0 = Source{1}[Data]

 

[Chippy68]

Thanks for this but I don't know where to put this or the steps to do it. I have never used power query....Can you walk me through it?

I have it automated through google sheets because I was able to find a step by step walk through but I want it in Excel.

Thanks

 

[DRSteele]

You really shouldn't be cross-posting.

No one can really 'walk you through' how to use PQ by typing it out in a forum thread.

​

This thread you started has instructions on how to learn about PQ. https://www.mrexcel.com/forum/excel-questions/999389-masters-live-scoring-new-post.html