Saturday, February 06, 2016

Using filled maps in Microsoft Power BI for provinces, regions and counties in European countries

A couple of weeks ago I wrote a blog post Problem with filled maps (choropleths) in Power BI for Belgian provinces in which I stated that there are some problems with using filled maps for international locations. Thanks to the excellent support of the Power BI team I managed to resolve the issue for the Belgian provinces.

If you ever want to use filled maps with Power BI it is important to use the correct data category and this can be quite confusing for Europe where we have a multitude of different ways of dividing up a country which do not always correspond to the way it is done in the U.S. The data categories for locations that you can use on a filled map are Continent,Country/Region, State or Province and County .



So when I switched the data category for my Belgian provinces to county – the filled map already looked at lot better but not completely since it shows the Grand Duchy of Luxembourg (https://en.wikipedia.org/wiki/Luxembourg)  and not the province Luxembourg (https://en.wikipedia.org/wiki/Luxembourg_(Belgium) ) on the map.


So I tried it out for a number of neighboring countries - the next table gives an example of what the different data category labels (in bold in the first row) correspond to for a number of European countries.

I tested it with Belgium, Germany, the Netherlands and France. I did not get it the regions to work correctly for  France but this might be because France has officially merged a number of regions since January 1st. France is also subdivided into a number of departments  and these were shown on the map as expected.
Power BI Data Category to use State or Province County Remarks
Belgium Flanders, Walloon region and Brussels Capital region Antwerp, East-Flanders, Flemish Brabant, Limburg, West-Flanders, Hainaut, Liège, Luxembourg, Namur, Brabant-Walloon Province Luxembourg not depicted correctly
Germany The different bundesländer: Baden-Württemberg, Bavaria, Berlin, Brandenburg, Bremen, Hamburg, Hesse, Lower Saxony, Mecklenburg-Vorpommern,North Rhine-Westphalia
Rhineland-Palatinate, Saarland, Saxony, Saxony-Anhalt, Schleswig-Holstein,Thuringia
   
France   The different departments as outlined on https://en.wikipedia.org/wiki/List_of_French_departments_by_population . I removed the overseas departments to make it workable
Netherlands The different provinces: Drenthe, Flevoland, Fryslân, Gelderland, Groningen, Limburg, North Brabant, North Holland, Overijssel, South Holland, Utrecht, Zeeland    
Different German states with the GDP per capita.



French departments with population density.



Dutch provinces with population density




1 comment:

Tom Devolder said...

Jeroen

Thank you for your insights on getting the Filled Map visualisation to work in Power BI. Very confusing that the "Data Category" option is not available in the Edit Query screen, but only on the modelling tab of the main screen...

Here's how to solve the problem with the Belgian Province/County of luxembourgh: I added to my dataset an extra column in Power BI, data type 'county', which combines the province/county name (f.E. "Luxembourgh") with the Country name. So I get for each data row "West-Vlaanderen, Belgium", "Luxembourgh, Belgium", etc.

That solve the problem.

But a new problem occurred: Filled Maps does recognice "Brussels Hoofdstedelijk gewest", but is not selectable in the filled map. I assume the fill for Flemish Brabant is on top of it, cousing this problem. Doe you havfe a solution?