OK, so it has been a while since my last post. Too busy? Lack of inspiration? Tired fingers? Lots of excuses but I finally got around to dusting off the keyboard so brace yourself for some Power BI.
If any of you have not yet taken a look at Power BI then you are missing a treat. It has come a long way since the early days of the Excel plug-in with a great desktop app which is free to download – so what are you waiting for? I will be presenting a workshop and session at the Digital Workplace Conference in Sydney coming up in August so you have no excuses. Well maybe just finish reading this first?
Maps can really give your data some visual context and they are really easy to do in Power BI but there are a few little tips to prepare your data so that is what I am going to focus on today.
The first thing I am going to do is grab the Australian States and Territories from a web page.
Select Get Data and choose Web as the source. Then paste in a good URL for your data:
Clicking OK will take you to the Navigator page which lets you choose which data on the page you would like to include. In this case I want the Statistics table so I tick that and click Load:
So now we have some geographic data we are ready to create a map right? Well not quite. The problem is that there seem to be other geographic places around the world with the same names, so just creating a map as it is will likely give you some unexpected results – and using abbreviations is even worse.
The first thing I want to do is add a column to our data. I could do this through the Data view but I want to try out a cool new feature so use Edit Queries and select Add Column from the tabs at the top of the page. Select the State / Territory column and then choose Column From Examples -> From Selection:
Type into the first row of the column created:
Australian Capital Territory, Australia
You will notice that Power BI automatically assumes you want the same pattern applied to all the other rows too:
Click, OK then Close and Apply and you now have a column which also references the country. Even I could not confuse that with Victoria in South Africa.
One last step.
Select the new column – I have left it with the default name Combined – and from the Modelling tab choose Data Category -> State or Province:
We are now ready to create our map. In the Report view click on the Combined column and Land Area km2 and then choose the Map or the Filled Map:
Nice work! We got to load data from a web site, add a column by example, correctly categorise our data and create a chart. Not bad for a few minutes work!
Have fun – and do come say hello at the conference if you are attending.