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.

Using Power Query and Power Map to visualize public data of the windmills in denmark

While preparing a training session for a customer within the Windmill industry I found public data that lists all the windmills in Denmark with their size and location.

The data can be found here – http://www.ens.dk/info/tal-kort/statistik-noegletal/oversigt-energisektoren/stamdataregister-vindmoller

So I decided to see what Power BI could do with this dataset.

The data

The file with the latest data (http://www.ens.dk/sites/ens.dk/files/byggeri/anlaegprodtilnettet.xls is named the same way and is structured in a way that makes it easy to make custom analysis and visualisations.

So first tool to use in the Power BI stack…

Power Query to the rescue

First – switch to the Power Query tab and choose to load data from File and Excel File and paste the address to the file in the file open – it does take some time but it will work.

And Power Query will open and list the tables that exists in the file

The data we are interested in starts at row 18 and contains a lot of columns that we aren’t interested in.

And there is also some formatting of columns from text to numbers etc.

Here is the total list of steps shown

Create a Power Query function to create Latitude and Longitude

The data file has doesn’t contain Latitude or longitude but the coordinates is listed with the “European Terrestrial Reference System” – so I had to find a way to convert the values into Latitude and longitude.

The Danish Geodata Agency provides a free webservice that can do the conversion for us – http://geo.oiorest.dk/documentation/api/koordinat.aspx – examples.

So for instance the first wind mill is located at

http://geo.oiorest.dk/wgs84.html?etrs89=6171175,720898.4

And inspired by Rafael Salas – blog post – http://www.rafael-salas.com/2013/08/power-query-creating-function-to.html – I decided to create a Power Query function to do the calculation.

So I created a blank Query and added the following query

The function takes two arguments the east and north coordinates, and uses those coordinates to get and XML table from the Web service. As I run with a comma as decimal separator on due to my Danish regional settings I have to replace the comma with a dot as the web service requires the decimal separator to be a dot.

The result is loaded into a table with the “bredde” – latitude and “Længde” – longitude

And the function returns both of these columns.

Then I can use that function in my first query by adding a Custom Column.

And expand that to get both latitude and longitude as columns in my query.

And voila the custom calculated columns.

Then some number formatting of some columns and renaming to English heading and we are ready to send the data to Excel

The data back in Excel

Returning the query to Excel will then give me the list of all 5.126 running windmills in Denmark per September 2013.

Pretty awesome – it does take about 2-3 minutes for it to update/lookup all the geo location – but only one isn’t matched and that is due to an empty row.

Using Power Map to visualize the data

With the data nicely washed and geotagged we can use Power Map to visualize the data.

Power Map’s first guess on the geomapping is actually very good. The “Kommune” is set to county – which is correct and because I named the columns Latitude and Longitude these are automatically also linked correct.

With the Geography properly matched we can move “Next” to visualize the data.

So for instance the KWH by Supplier

Or the KWH by Region/County

Or the world biggest sea wind mill park

Or a heatmap

This is so much fun and finally let make a video of the development over time.

Power map video

Power map also enables us to create a timeline and play the timeline. The data has the date of when the windmill was established so we can use that date to visualize how the windmills have evolved over time.

So by adding the established field to the Timeline – power map can visualize the development over time.

This can be created as a video.

Creating the 45 sec video at the medium format took about 10 minutes to create – so be patient.

Here is a link to the video – http://sdrv.ms/17cBIrx

The file

You can download the example file – here

Comments please

You are more than welcome to add a comment whether you find this cool or not J

Analyzing foursquare data using Excel Power Query and Power View

I have been on foursquare for a couple of years now, and have also created venue for my workplace where I am the administrator. This is required for making the report in this blog post but there are many API end points where you can access your own data to create reports on.

You can read a lot about the different possibilities her – https://developer.foursquare.com/docs/ .

I wanted to analyze the stats on my Venue – Knowledge Cube A/S – and used this endpoint to retrieve the data from Foursquare – link

You need to find the Venue_ID to retrieve the stats and you can do that by Search API – or by checking in on the venue and use https://developer.foursquare.com/docs/explore#req=users/self/checkins to find the venue id in the response.

So let’s get the data using Power Query – Switch to Excel and on the Power Query tab choose the From Web in the Get External Data group.

This will open up the Power Query window and you can construct/design the query.

The Foursquare api will be return as a JSON document and we can browse through the response using the navigator.

But first start by naming your query to something meaningful instead of Query1 – do that by double clicking the name and type the new one.

When we navigate through the response we can see the different elements we get returned for Foursquare.

And could choose to see the checkins by agebreakdown by simply clicking on “list” it will return a list of records and in order to extract those you convert it into a table.

Choose None as delimiter and choose ok

Expand column1

And you get a table with the Age groups.

In my example I want the top visitors so I use the navigator to go back in the reponse and Power Query will want me about this – but its ok in this case.

So to get the topvisitors I choose that from the stats and convert it into a Table.

Expand the Column1 to get the users

And then expand the user to get the meta data about the users.

In this case I didnt pick all but just a few informations that I wanted to visualize – Name, Photo, home city and number of check ins.

When I click ok in Power Query the data is returned to Excel as a table containing the top 10 visitors of my venue.

I then add this Power Query to the data model by click “Load to data model”

As I would like to have a picture of the person I construct a url to the profile picture – BE AWARE that you need to add “Original” between the prefix and suffix.

Be sure to check that the datacategory for the calculated column is set for Image URL

For the HomeCity column you should specify City as Data category.

Finally add a measure to SUM the Checkins.

Switch to Excel and insert a Power View Report

And after a few clicks you could have this reports showing the Top Foursquare visitors of your Venue.

PS – Haven’t spent time on renaming the column names but this should offcourse be done

Use slicers to select the facts in a pivot chart

In one of my previous posts link – I described how you can create a dynamic pivot chart in Excel 2013 – but it did require VBA to change the fact.

But inspired by Rob Collie – link@powerpivotpro and several readings about the DAX SWITCH function – I decided to see if I could use slicers to create a dynamic chart using Slicers.

So first I created a power pivot model based on Adventure Works DW, and created some facts

Sales Value

Sales Value LY

Sales Units

Sales Units LY

Average Price

And the user should now be able to select from a slicer which of these should be plotted in a development chart.

So I start by creating a table in Excel with the facts

Adds the table to the data model in the workbook

And create a measure called

Selected fact:=min(Table1[FactID])

This will be used in another fact which is the one I will use in the pivottable/chart.

So when I create a pivottable the Selected fact will return the ID of the fact when the Fact names are placed on rows or columns.


Then I create a dynamic fact calculation

Fact to plot:=SWITCH([Selected fact];1;[Sales Amount];2;[Sales Amout LY];3;[Sales Units];4;[Sales Units LY];5;[Average Price])

So adding the fact to the pivottable it looks like this – with the correct value under each fact – DAX magic 🙂

You will get a warning about missing relationship but you can ignore this.

So Insert a slicer with the Fact as the slicer


And now I can filter the table with the slicer


And now you can create a pivot chart based on the pivot table and add a slicer for the year and other dimensions if needed..


OBS – you cannot use this technique with the new pivotchart in Excel 2013 but you have to have a pivottable behind the chart – but you can put that on a hidden sheet if needed.

You can download the example file from here – Download

Plotting Growth and Fall on a Bing Map in Excel 2013

As a short follow up on my previous post about using the Bing Map Office app for Excel – I tried to see if I could use it to plot growth and decline on the map.

And If you can live with a bubble with the same size and a small white stripe in bubble (pie) – it can be done.

Simply create a table with a column that has a value for the up and down in separate columns, and plot them.

Then in the settings for the map, you pick a green color for up and red for down.

And now you have an indicator for growing and declining areas.

Now lets hope that Bing adds more features to the app so we could have plot a bubble and vary the size after a specified value.

Using Bing Map App for Excel 2013 to plot break-ins in Denmark

Slowly we see more and more Apps for Office and one of the highest rated is the Bing Map App.

Here is a link to it – link.

The app lets you select values in Excel and plot them on Bing Map.

Let me just step through how you can do this.

First find some data to – in this case I went to http://www.politistatistik.dk/parameter.aspx?id=27 to extract information about crime reported in 2012 about Break in’s in private houses.

This will give a table like this

But in order to plot it we have to transpose this to a table format. So after a little bit of Copy, Paste Special – Transpose – we can get to this table

And we are ready to map the information – but first we need to activate the Bing Map App for Office – this is done via the Ribbon – Insert and Apps for Office.

If the app isn’t listed under My Apps – you can use the “Find more apps at the Office Store” and search for it.

When you insert you might get this warning.

And when you click Start – you will be welcomed by the App.

Select the table of data and click the Icon – this will plot the selection on the map (please notice that it has a max of 100 points to plot)

Then you will see the municipalities plotted –

But our data has two problem – apparently Denmark has municipalities in USA and Italy.

This is because Bing Maps needs a little bit more information to plot Ringsted – Denmark instead of Ringsted, IA – luckily it’s very easy to fix.

If you modify the label to plot – from Ringsted to Ringsted, Denmark or Ringsted, Danmark – Then we will help Bing Maps to plot correctly.

And voila – all plotted correct in Denmark – and then you can start to play around with the settings and filters.

If you add more columns with data you can plot pie charts on the map – really cool.

Two more tips –

If you have the latitude and longitude – you can use that to plot by

Design a metro style dashboard in Excel

Yesterday I presented at SQL Saturday Copenhagen #196 how to create a Metro Style Dashboard using Microsoft Excel 2013 and PowerPivot.

I made this using standard Excel functionality and with no use of VBA, and heavy use of the CUBE functions to retrieve values from the datamodel.

As promised I have uploaded a copy of the model to Skydrive – Link

My presentation can be downloaded from Link

The macro to hide the Excel look is as follows – place it in your Personal Macro Workbook

Sub TurnExcelOff(bolState As Boolean)

Application.DisplayFormulaBar = bolState

Application.DisplayStatusBar = bolState

With ActiveWindow

.DisplayVerticalScrollBar = bolState

.DisplayWorkbookTabs = bolState

End With

With ActiveWindow

.DisplayGridlines = bolState

.DisplayHeadings = bolState

End With

End Sub

Sub ExcelOn()

TurnExcelOff True

End Sub

Sub ExcelOff()

TurnExcelOff False

End Sub

If you have any questions please feel free to comment on this post and I will try to answer.

PS – Thanks to all the people that arranged a great SQL Saturday event #196 in Copenhagen.

Presenting at SQLSaturday #196 Copenhagen

I am presenting at the first SQLSaturday event in Denmark, with my session “Create a metro style dashboard using Excel and PowerPivot”.

First of all I think the community has done a great job to bring the SQL Saturday concept to Denmark, and hope that the event can become such a success that we can do it many times. Sharing knowledge and learning from the SQL Pass community is really a great opportunity for all SQL professionals.

My session will focus on how to use Excel 2013 to build a dynamic dashboard in the Metro Style look and feel based on a Powerpivot model.

I will demonstrate

  • How to build a PowerPivot model
  • How to extract data and values from the PowerPivot model using the Excel CUBE functions instead of using pivottables
  • How to create dynamic sets to get the latest periods
  • How to create a parameterized DAX table
  • How to design a metro style chart
  • How to create Metro Style KPI boxes in Excel

I am really looking forward to my first speaker session and hope that the session will bring new learnings to you.

See you at SQL Saturday #196

Create a scrollable chart linked to Power Pivot data

Creating a Chart with many point can sometimes give some pretty irrelevant charts.

So I tried to see if it was possible to create a chart where I could scroll through the products based on their, and by using the old Forms Scroll bar control (you need to show the developer tab in the ribbon) it was quite easy.

I can now scroll through the records and as Excel 2013 has nice animations it looks fantastic.

So – How to

I created a connection to the sample tabular Adventure Works model on my local machine and named the connection AdventureWorks.

 

And then I created the SET of Products that I wanted to display.

=CUBESET(Connection_name;”FILTER([Product].[Product Name].Children, [Measures].[Total Sales])”;”Products”;2;”[Measures].[Total Sales]”)

The Filter is used to filter out products with no Sales and the last two arguments specifies that I want the set sorted after the Total Sales in Descending order.

Then I created the data that I wanted to plot in chart.

 

 

The first rank cell is linked to the scrollbar control (will show you later how that is done) and product and Sales Value is calculated via

=CUBERANKEDMEMBER(Connection_name;$I$3;G7)

And

=CUBEVALUE(Connection_name;H7;”[Measures].[Total Sales]”)

The other rank values is calculated by adding 1 to the previous cell.

Now you can create the chart on the area

 

And then to make it scrollable you add a Scollbar from the Form Contols.

 

Right click the scrollbar and choose format control – and set the cell link to the first Rank in the table and specify max value and minimum value to 0

 

 

The max is set to the number of items in the set minus 10 – this could be automated with VBA.

You can download the example from here – (Will only work if you modify the connection to your own AdventureWorks model)

 

 

 

 

 

 

 

 

 

 

How to use Data Explorer for Excel to extract data from Statistics Denmark

StatBank Denmark contains detailed statistical information on the Danish society, and they are exposing a lot of interesting statistics about Denmark.

Unfortunately they do not expose these data in oData feeds, public web services but only via an interface where you manually export the tables in excel, csv, html or similar formats. But then I noticed the other day that you have the possibility to access the your saved tables as XML and as Microsoft “Data Explorer” Preview for Excel handles this perfectly – I thought that I would give it and try so let me take you through the process.

Step 1 – Log on to Stat bank Denmark

To select the data you need to logon to StatBank Data – http://www.statbank.dk/statbank5a/default.asp?w=1920 and you should create a use account so you can extract large tables and most important – you can save your queries and that is important.

Now you have access to all kind of interesting things about Denmark.

Step 2 – Create a query

Let try and analyze the “Live births by age of mother and sex of child”.

I choose all the elements in the different available filters and click Show table. The result is this table

And if I choose to export this to CSV or Excel I get a table that is not very useful for further analysis.

Step 3 Save the query

At the bottom of the page you can select to save your table for future use and also specify whether the timeperiods should rolling, fixed or only latest updated periods.

Give the report a name and click “Add as new saved table”.

Then you will get a id for the table in the next picture and this ID is important for that will be used to generate the xml file later on.

You can also find the ID in the link info at the status bar.

Step 4 – Test the XML feed

Now can get the XML via calling

http://www.statistikbanken.dk/xml/167000

Step 5 Get the data into Excel

So choose Import XML from the Data Explorer tab and enter the http://www.statistikbanken.dk/xml/167000

This takes 10 – 15 seconds for the file to be loaded and data explorer to appear.

Then you can click the Data – in the navigator and see the table data.

Now we can format and rename the columns.

So final result

Click Done and the data will be returned to Excel

Step 6 – Load the data to Power Pivot

In Query Setting Task pane – Select not to Load to worksheet and click load to data model

And now I can refresh data directly from PowerPivot

Step 7 Analyze the data

And now that you have the data in Power Pivot you can start to analyze and visualize the data using Power View or just the standard Excel charting tool

Or

Step 8 – More data

And now you can import other relevant data from Statbank to see what might have an impact on the birth rates.

You try

You can find the example file here – and the cool thing is that you can use the same query that I have created in my user profile on Statbank.

Links

Download Data Explorer

Stat bank Denmark

The xml data