Processing detailed WKT polygons for Icon Map: Power Query and DAX magic to exceed the 32,766 text character limit in Power BI
Sometimes we want to display highly detailed spatial files in Power BI using WKT text strings in the Icon Map custom visual… BUT Power Query has a text character limit of 32,766 characters per data point - so what can we do?!? In this video blog I walk through some Power Query and DAX magic to split up our WKT text string and then stitch it back together using CONCATENATEX. I also go over a very frustrating ‘gotcha’ where Power BI removes the lagging spaces in our text string on import (which causes errors in our WKT file) and how to resolve it. I hope this helps you have fun exploring maps in Power BI.
🎦 Check out the video 🎦
🤔 Why does my Icon Map not render?
Icon Map is my favourite mapping visual in Power BI, as it allows for such flexibility and customisation - including adding your own custom polygons and lines as WKT text strings. However, a challenge that crops up time and again is that my spatial files are very detailed, which can result in them not rendering in my map (without a bit of processing first).
The key culprit as to why they are not rendering is often that the WKT text string has exceeded the Power BI cell character limit (of 32,766 characters for a single text value). If you exceed this limit - Power BI imports the data from Power Query, but simply truncates the text value at the maximum character limit (with no warning!!!). Therefore, it's not a valid WKT string, so it fails to render in Icon Map.
So we need to do a bit of clever Power Query and DAX magic to make it work...
🪄 What options do we have?
For this example, I'm using the very detailed Estuary Drainage Catchment shapefile for NSW (which I downloaded from NSW SEED database here). The first step is to convert the Shapefile to a WKT (or well known text) CSV file, using a converter program (like the QGIS - check out my blog here for more details). Quickly opening the CSV file in Excel - we can see an immediate problem, where the text character limit has been exceeded for a number of polygons (interestingly, Excel allows one extra character, where the limit is 32,767).
So we have several options available to us:
Reduce the precision: by reducing the number of decimal places in the coordinate pairs for the polygon vertices. You can see in my example the long - lat pairs are to 12 dps (149.882448887925 -37.4566315396983) which takes up a LOT of characters. I'd recommend truncating at 4 dps, which I've found works well. You can do this using the 'Snap to Grid' tool from the QGIS processing toolbox.
Simplify the layer: If the spatial layer has been derived from some algorithm (such as a catchment or river delineation function) it can result in a LOT of vertices (which adds to your text string). You can look to simplify the layer to reduce the number of vertices, noting that this could reduce the accuracy of your layer if you need it to match exact boundaries. MapShaper is a great tool for this, as it preserves the snapping of adjacent polygons (where the simplify tool in QGIS results in small discrepancies at the boundaries of adjacent polygons).
Split then concatenate WKT text strings: The last option is to import the large WKT text strings, and do some Power Query and DAX magic to process them so they can be rendered in Icon Map
In this blog, we’re looking at option 3 in more detail. For this, I've adapted the approach from Chris Webb's blog here, which he applied to processing Base64 text data for displaying images, but the principle work the same.
👇 How to split then concatenate long WKT text string:
The key steps to processing your large WKT text string in Power BI are:
Import your WKT file into Power Query
Select the WKT column, and select 'Split column' then 'By position' and choose 30,000 (I use this as it's a round number, but you could use 32,766). Click on the 'Advanced options' and select split into 'Rows' (not columns). This will split the WKT text string and insert new rows for each segment (up to 30,000 characters).
Insert an index column ('Add column' then 'Index Column'). We'll use this to sort the WKT string segments.
Close and Apply
Add a new DAX measure using CONCATENATEX to concatenate the WKT text string back together.
Create your icon map, with the Object ID (or catchment name, etc.) in the category, some dummy data in the size field (as we don't need this for WKT files, but Icon Map needs it to render) - I use Count of Object ID, and use your DAX measure as the dynamic WKT text string.
You should see (most / hopefully all) of your polygons rendering now on the map. Note - as we have a lot of data, and are using an iterator function (CONCATENATEX) the performance of the map can be pretty poor - but that's the trade-off for displaying detailed data.
Note - if you aren't seeing all of your polygons render as expected, you might be experiencing the issue that I faced, where when you import a text string from Power Query which has a space as the last character (which happens if the split occurs between the coordinate pairs), then it deletes this space!! This drove me nuts trying to figure out what was happening - but once I knew the rule, then I could correct it using a DAX calculated column. I just applied a rule if the first character of my text string was “-” (i.e. the negative sign before the latitude value, as I'm in Australia so our Latitudes are all negative...) then add a space, otherwise leave as is. And this worked - YIPPEE!!
While this approach does work, as mentioned, it can have performance issues where the map is slow to render. If possible, always try to reduce the precision of your shapefile and simplify your shapefile if possible (check out this blog post here where I walk through how to do this using QGIS and MapShaper), and then use this 3rd approach as a last resort. But it is a handy solution to have in your toolbox...
I hope this is useful for your study - and please reach out if you have questions, improvements or other mapping or environmental challenges in Power BI you'd like the DiscoverEI team to investigate.
💙 Connect
We'd love you to connect with the DiscoverEI team:
Email: info@discoverei.com
Website: https://www.discoverei.com/
Blog: https://www.discoverei.com/blog
YouTube: https://www.youtube.com/channel/UCFF5eaUsht-WzTdjEAgPDtQ
LinkedIn: https://www.linkedin.com/company/19050025/
Twitter: https://twitter.com/discover_ei
PowerBI4 Enviro's Meetup: https://www.meetup.com/en-AU/powerbi4enviros/
If you want to learn more about Power BI mapping visuals, then register for our Power BI 4 Enviro’s monthly meetup (https://www.meetup.com/en-AU/powerbi4enviros/), or if you’re keen on presenting at a future session then contact Christian (cborovac@discoverei.com).
Power BI Training Courses
If you’re interested in learning from our team, then we have a range of 2-day Power BI training courses available to fast-track your Power BI journey.
Click on the links below to learn more, and get in touch with the team today!
Purchase Training
Do you want to learn how to design engaging and intuitive Power BI reports, which communicate your key insights at a glance and tell your data story?
Our two-day Power BI training course is designed specifically for Power BI Professionals and Data Analysts, and provides our best practice tips, tricks and hacks to help you transform your data! We provide this course online, in-person group sessions, or customised in-house training for your team. Places are limited (max 8 participants per class) so secure your spot today!