Forum Discussion
exclude areas with postcodes on map
Hello Everyone!
Could you please help me with an issue I'm facing regarding creating a filled map in Power BI?
I have a table listing the countries where we are able to conduct transport operations. However, there are certain areas within these countries where transport is not possible. I want to highlight these restricted areas in a different color on the map. The restricted areas are defined by the first number or letter in some cases of the postcode in each country, but I don’t have a comprehensive list of all postcodes in europe.
Is it possible to achieve this in Power BI? Here is an example of my table.
Thank you in advance,
Katerina
Ah I see. I don't think it is possible to visualise postcodes on filled map visual without having the entire postcode in your data source. So I am pretty sure you would need a dataset with all the postcodes.
I found this one for free and there are a couple paid options it looks like. You would need to load something like this into PowerBI, and then do the query steps I described above to flag which out of all postcodes is restricted. Although this would be quite a heavy query for just this one visual...
If the restricted postcode rules do not change often (less frequently than a year) I would find some online visualiser tool where you can highlight specific postcodes on a map, and save it as a static picture instead.
Sorry I can't be of more help!
4 Replies
Hi Katerina_SL
If I interpret your example table correctly (No restricted areas in Germany and Croatia, postcode beginning with 8 or 7 in Greece, 20 in France and ZE or KW in UK are restricted) you could try the following:
modify your restricted areas query so each restricted area shows up in its own row (one way to do this is to remove the {}, split by delimiter (,) and unpivot the resulting columnscreate a new colum that combines the country name with the restricted prefixes ("Greece8" "Greece7" "United KingdomZE" etc..)
in your main table that contains the full postcodes, create the same type of custom column with the Country name + the first (or first two) characters of the postcode (Text.Start in custom column)
Merge the two queries on this custom column, expand, and if they have a value they are restricted postcodes. You can use this to create yet another custom column that contains a hex color code, or a binary / true/false value to control the color or visibility of the restricted postcodes in your map visual
I hope this helps, but if something is unclear please let me know. If possible, it would be great if you can upload an anonymized sample of the data you use to populate the map visual, so I can try to replicate the solution in practice.
Best regards,
Daniel
Hello Daniel!
Thank you for reply. You understand me correctly. But the problem that I don't have any main table with postcodes. I have only table with restricted areas.
And the question is if it's possible to do it without main table with postcodes? Or should I export from some site this data?Katerina
Ah I see. I don't think it is possible to visualise postcodes on filled map visual without having the entire postcode in your data source. So I am pretty sure you would need a dataset with all the postcodes.
I found this one for free and there are a couple paid options it looks like. You would need to load something like this into PowerBI, and then do the query steps I described above to flag which out of all postcodes is restricted. Although this would be quite a heavy query for just this one visual...
If the restricted postcode rules do not change often (less frequently than a year) I would find some online visualiser tool where you can highlight specific postcodes on a map, and save it as a static picture instead.
Sorry I can't be of more help!
