Create the leaderboard of the U.S. Open Golf Championship in Excel with Power BI #usopen #powerbi

Here is an example of how you can use Power Query and Excel’s Power BI features to create a leader board of the U.S. Open Golf Championship 2014.

First I found a website that had a leaderboard …

Then switch to Excel and activate Power Query and choose to Get external data from Web

The navigator will then give us the opportunity to select element from the page and I can see that Table 0 on the page contains the leaderboard table

A double click on the table will take you to the Power Query window and we can start transform the data.

 

In order to make the values ready to import into a table or directly into the datamodel I perform a number of transformations

• The POS column – Search and replace the T for tied with nothing
• The TO PAR, TODAY columns – Search and replace E to 0 to convert the equal par to 0
• The columns R1-R4 and TOT – Search and replace – to 0
• The column THRU – Search and replace FOR F to 18
• The columns POS, TO PAR, TODAY, R1-R4 and TOT is the datatype changed to Number

The steps is the Advanced Editor is like this

let

Source = Web.Page(Web.Contents(“http://espn.go.com/golf/leaderboard”)),

Data0 = Source{0}[Data],

ChangedType = Table.TransformColumnTypes(Data0,{{“POS”, type text}, {“START”, type number}, {“CTRY”, type text}, {“PLAYER”, type text}, {“TO PAR”, type text}, {“TODAY”, type text}, {“THRU”, type text}, {“R1”, type text}, {“R2”, type text}, {“R3”, type text}, {“R4”, type text}, {“TOT”, type text}}),

ReplacedValue = Table.ReplaceValue(ChangedType,”T”,””,Replacer.ReplaceText,{“POS”}),

ReplacedValue1 = Table.ReplaceValue(ReplacedValue,”E”,”0″,Replacer.ReplaceText,{“TO PAR”, “TODAY”}),

ReplacedValue2 = Table.ReplaceValue(ReplacedValue1,”F”,”18″,Replacer.ReplaceText,{“THRU”}),

ReplacedValue3 = Table.ReplaceValue(ReplacedValue2,”-“,”0”,Replacer.ReplaceText,{“R1”, “R2”, “R3”, “R4”, “TOT”}),

ChangedType1 = Table.TransformColumnTypes(ReplacedValue3,{{“POS”, type number}, {“TO PAR”, type number}, {“TODAY”, type number}, {“THRU”, type number}, {“R1”, type number}, {“R2”, type number}, {“R3”, type number}, {“R4”, type number}, {“TOT”, type number}})

in

ChangedType1

Then we can name the query and select Apply and Close from the File menu.

 

And now I have the leader board in Excel

 

 

And to make it update automatically every minute you can set the connection to refresh every 1 minute

 

And then we can start use Excel and powerview to create over own design of the leaderboard.

 

And add databars to To Par column

 

You can download the model from here – Link.