Tag Archives

5 Articles

Nederlandse postcodes in Power Map (Support for Dutch postal codes in Power Map)

by Jeroen ter Heerdt 7 Comments

Normally I blog in English, but since this post is specifically about support Dutch postal codes in Power Map, this post will be in Dutch. For those interested, the answer is: Dutch postal codes are supported in Power Map, but you need to use the first four digits and strip off the last two characters. Better stil: use the address (if you have it).

Een veel gestelde vraag van mijn klanten is: is er ondersteuning in Power BI / Power Map (Geoflow) voor Nederlandse geografische informatie, zoals postcodes, steden en adressen? In deze post wil ik antwoord geven op deze vraag met de volgende dataset:

Met deze data kun je gemakkelijk een Power Map maken door in Excel op Insert / Invoegen te klikken en dan voor Map te kiezen. Als Power Map gestart is worden Address en City al automatisch op de kaart geplaatst. Klik op ‘Postal Code’ en kies ‘Zip’ om deze ook aan te zetten (zie onder):

Nu, als je Address aanklikt dan wordt de data perfect geplot op de gekozen locaties:

Dus: Nederlandse adressen werken prima.

Als we vervolgens overstappen naar City, dan ontstaat het volgende:

Opnieuw, klopt perfect.

Echter, als we nu ‘Postal Code’ kiezen dan krijgen we een lege kaart. Dit komt omdat Power Map op dit moment alleen de eerste vier getallen van een Nederlandse postcode kan ondersteunen en niet de volledige vier getallen plus twee karakters. Dus 1000 AA werkt niet, maar 1000 wel. Natuurlijk is dit minder specifiek dan 1000 AA. Als je dus de laatste twee karakters van de postcode afhaalt werkt de mapping weer:

Concluderend: Nederlandse geografische informatie wordt ondersteund en als je adressen hebt kun je zelfs heel specifiek plotten. Als je alleen postcodes hebt kun je op dit moment niet de volledige postcode gebruiken, maar alleen de eerste vier getallen. Overigens kun je met die vier getallen wel regions laten plotten op je kaart 🙂

Analyzing Amsterdam Crime Rates with Power BI

by Jeroen ter Heerdt 0 Comments

Here is another example of what can be done with Power BI. This time I attempted to analyze crime rates in Amsterdam.

I retrieved burglary and burglary attempts figures from the Dutch Police website: http://www.politie.nl/misdaad-in-kaart/lijst?geoquery=amsterdam&categorie=1&categorie=2&pageSize=500&page=1

There are three pages here, so I use Power Query to get all three pages into Excel and then appended them together in one big table. I did this using three ‘from web’ commands in Power Query, so I got the following queries:

Next step was to append the three tables together. I started with the ‘append’ command in Power Query which allowed me to append Page1 and Page 2 together. A quick edit of the code gave me the result I was looking for:

let
 Source = Table.Combine({Page1,Page2}),
 Source2 = Table.Combine({Source,Page3})
in Source2

And the resulting table:

Next, I loaded the table to PowerPivot, added a quick sum and then opened Power Map by going to InsertĂ Map in Excel.

In Power Map I created three layers, one using shapes, another one using a heat map and a third using a column graph. Then I created a tour using these layers. The resulting video is below.

As you can see, it is really easy to visualize geographical data using Power BI in Excel! Pretty cool huh?

Power BI Demo Contest

by Jeroen ter Heerdt 0 Comments

I want to draw your attention to a contest that we run: the Power BI Demo Contest. All you need to do is create a short video of something cool you built using Power BI and share it. You could win great prizes!

More info is on Facebook: https://www.facebook.com/microsoftbi/app_112813808737465

Good luck!

Taking the new version of Power Map for a spin

by Jeroen ter Heerdt 0 Comments

One of the most exciting tools in Excel has recently released a new version! Get the updated version of Power Map here: http://www.microsoft.com/en-us/download/details.aspx?id=38395 (Excel 2013 only).

This update comes with a ton of new features, including some of the most popular feature requests I have come across.

One of the first things that you will see when you add data to Power Map is that Power Map is now more intelligent and automatically suggests how to interpret columns. In the screenshot below it automatically mapped the ‘Gemeente’ (County / Municipalty) and ‘Provincie’ correctly. This goes to show that this automatic mapping not only works with English terms J

 

The way you choose between types of column charts has changed. You just choose the ‘Column’ type and then on ‘Category’ you can change the chart sub type (clustered or stacked).

Power Map can now also use calculated columns and supports hidden fields. Annotations can now not only contain custom text or fields but can also display an image.

A new visualization type is ‘Region’, which is actually really cool. It provides shapes as available from Bing to overall items on the map. As far as I can see it not only works for the obvious countries, but also for example for provinces in the Netherlands and even the municipalities.

If you have chosen a category for this new type of visualization you can also change the way regions are shaded, for example by relative values in the same category or across all items.

Then, with a tick of a button you can change from a 3D map to a 2D / flat map (note the cool animation).

Also, it is now possible to save your tour to a video right in the tool. Video types can be optimized to presentations and HD displays, computers and tablets and for mobile devices.

One great new feature is that it is now possible to change the coloring of charts, regions and bubbles:

Here is a sample tour I created in 20 minutes based on population data that I loaded from CBS (Central Bureau of Statistics in the Netherlands) using Power Query (hint: make your selection in CBS’ Stat line and use the “from web” option in Power Query to get the data in Excel).

 

Power BI and why you should care

by Jeroen ter Heerdt 2 Comments

Yesterday Microsoft announced Power BI for Office 365: a self-service Business Intelligence solution delivered through Excel and Office 365. Power BI ties together the various bits and pieces we already had (Power View, PowerPivot, GeoFlow, Data Explorer) and also introduces some exciting new functionality. In this post I will introduce you to Power BI and discuss the various capabilities. Future blog posts will deal with the components more in-depth.

(By the way, do not let the tag “for Office 365” set you back; Geoflow Power Map and Data Explorer Power Query are available as add-in for Excel regardless of whether you use Office 365 or not).

Power BI
The image below shows the Power BI platform. I have broken it down in two segments: Excel and Office 365.

 

With Power BI we take the next step to making Excel a true BI tool. BI developers used to smile when I talked about Excel and told me Excel helped end users create non-transparent, spaghetti like BI solutions. I have to admit, that is true. Now with Power BI anything you do with data in Excel, from loading and cleansing using Power Query, modelling and enriching it using PowerPivot and finally displaying using Power View and Power Map is structured and traced. No more page long formulas. No more copy-paste, hidden sheets, linked formulas and other nightmares for us BI folk.

 

ETL – Power Query
Previously named Data Explorer, Power Query is our self-service ETL tool in Excel. Power Query can connect to just about any data source you throw at it and it enables you to load data, cleanse it and then use it in your Power Pivot model. It even includes a natural language search function that helps you find information in your organization or on the web if you do not actually know where the info could be at. Imagine have loaded something from your corporate data warehouse and then adding relevant external information (such as weather or population info) from the web without having to leave Excel! Once you have loaded data you can add and drop columns, change data types, split columns, combine tables, filter data, remove duplicates, etc. Power Query not only connects to “standard” databases and files, but also includes a connection to Facebook, Hadoop (HDFS and Azure HDInsight), SharePoint and any OData Feed. All steps you do in Power Query are stored in a script so it is clear where data came from, what happened to it along the way and where it got displayed. See my screenshot below: I did a web search for ‘population of European cities’ and clicked on a Wikipedia page to get the data in Excel.

 

Analysis – PowerPivot
PowerPivot has been out for a while and has gotten quite some attention. PowerPivot allows you to do data modelling with massive amounts of data in Excel. With massive, I mean huge. I keep repeating this as long as I keep meeting people who still think that Excel cannot handle 5 million records (that happened to me yesterday). With PowerPivot it is easy to load data from various sources, link them together (essentially creating a data model) and apply formulas. Using PowerPivot you create a structured model for your data in Excel. And it is fast. (Did I already mention it can handle lots of data?).

Continuing on from the example I started above, I added an Excel sheet with stores per city and their sales to the data model (the Excel sheet has just one sheet, which contains a simple table listing StoreID, SalesAmount and City). Then I related the two tables by dragging City from my Excel sheet to the Name column of the Wikipedia data I loaded using Power Query. The resulting data model is shown here:

Now I can do interesting stuff, such as add a calculation to figure out sales per inhabitant (Sales per Capita), by adding a column to the Stores tables with the following formula: =[SalesAmount]/RELATED(Cities[Population]) . (Dividing SalesAmount by the related cities’ population).

 

Reporting – Power View
Ah yes, Power View. The tool that is so easy to use that even my mom can use it (and its true). Power View enables you to create great looking, interactive reports with just a few mouse clicks right there in Excel. Just select what you need, decide how to show it and you’re done. Power View includes all the standard things: tables, matrixes, column charts, bar charts, pie charts. It also includes some great features that introduce time as a factor in your analysis by allowing you to create scatter plots with a play axis (think bouncing bubbles). Moreover, Power View can display images right there in your report and includes 2D mapping functionality.

 

In my example, with just a few clicks I created this report (I selected the Netherlands as country in the bottom right graph to show the highlighting capabilities in the other graphs). Also note the texts above each item to understand what is displayed here.

 

Geospatial – Power Map
Power Map (previously known as GeoFlow) is a very powerful 3D mapping tool. It allows you to plot any data on a map, as long as it makes sense. For example, just trying to plot your products on a map might not make sense. However, plotting your stores on a map makes a lot of sense. You do not need to specify longitude and latitude or other fancy stuff. Just some text is enough and the tool will go out and try to plot it on a map. Just try it, enter some city or venue names in Excel and click Insert Ă  Map. Two more clicks and you have plotted the information on a map!

In my example, here is what I created using Power Map. Again, this took me just two minutes:

(Above shows total sales amount and sales per capita per city, plotted on the 3D map).

 

BI Sites
A BI site is an optimized workspace dedicated to BI. You might call this a data marketplace: it is a one-stop shop where you go to get anything related to BI. You go there to consume a report, create a new analysis, share an analysis, discover some new insights using the items provided and find information.

 

Natural language query – Q&A
This is a feature I particularly love! It gets us closer to Minority report: just type what you’re looking for and we’ll find it and display it. Once information is published to the BI Site (for example through the Data Management Gateway (below) but also just by uploading an Excel sheet), you can search through all that information just by typing a question. In my example this might look like ‘sum of sales amount by country’. You can change the way the information is displayed by including ‘as map’ or ‘as bar chart’ to your question. I do not have a demo available right now, so I’ll just include a screenshot here. Here the user just typed ‘number of gold medals by country in 2008’ and the information is retrieved from an Excel sheet (note that the user has not explicitly asked to get data from that particular sheet) and shows it as a map (since we know this is geospatial information).

 

Manage and monitor
Power BI empowers data stewards; business users can grant access to published data sets based and track who is accessing the data and how often. This brings to mind the PowerPivot management dashboard we know and love.

 

Data Management Gateway
The data management gateway allows IT to build connections to internal data sources (think your data warehouse or other LOB information source) so reports that are published to BI sites can get that data easily.

 

Mobile Access
Last but not least: mobile access (woohoo!). Users can access their reports through a HTML5 enabled browser or through a mobile application on Windows or iPad. This means that Silverlight is no longer a requirement for accessing Power View reports. Other platforms might be added later.

 

And this is relevant…how?
So you have read this and maybe read some other blogs as well. You’re thinking to yourself: why should I care?

My question then is: do you use Excel? Well yes, any person who has ever worked with a PC has used Excel.

Exactly. That’s why you should care. You should care because the good old Excel which you though you knew so well has suddenly transformed into a cool kid on the block with lots of great and really easy to use features.

Those features enable you to find any data, work your magic and then gain insight from that data. Just think about that. How could you use this in your business? And in your personal life? (I myself am looking to buy a house. Power BI has allowed me to understand which neighborhoods I would like to live and which not, just by finding and visualizing data). I know there are specialized, paid, services for that (involves sending a text and paying for the info). I did it myself in half an hour, paid zero and learned a lot more about the question at hand.

Try out connecting to Facebook for example, and plot your friends on a map! Or find out who has not disclosed their gender to Facebook… J

This might be a revolution: Power BI brings the might of information analysis tools to anyone to consume any data for any scenario. The possibilities are endless. It is just a matter of using your creativity.

Since you are human, using your creativity is probably what you really want to do. Power BI: be creative with data.

%d bloggers like this: