Universal Power Query Function to Convert Eastings and Northings to Long and Lat for Power BI
In this blog I show you how we can convert our spatial data from UTM Easting/Northing to Latitude/Longitude using a Power Query custom function so we can make awesome maps in Power BI! I’d encourage you to watch the short video (link below) which walk through the why, what and how for converting your Eastings / Northings to long / Lat, but I’ve also provided step-by-step instructions and the PBIX sample file with the Power Query M-Code for download in the write-up below.
🎦 Check out the video 🎦
🤔 Why do I need to convert my spatial data:
Almost all mapping visuals in Power BI use longitude and latitude to render as the coordinate system. The exception is my favourite mapping visual Icon Map, which allows users to define a custom projection system for their map. However, this can be challenging and your data might not fall into just one projection system (like in Victoria, Australia which covers two UTM zones: 54 and 55). The data we've used in this demo falls into these two UTM zones, and has been sourced from the Victorian Water Measurement Information System here.
Therefore, we need a way to convert our data to longitude and latitude for mapping in Power BI. while it’s best to do this at the source using a mapping program like QGIS or ArcGIS, sometimes we don’t have the tools or skill to do this. But if you’re a Power BI guru - then we can also do it in Power Query!
🪄 Custom function in Power Query:
If your data is in Eastings and Northings - then you're in luck. The Power BI community has been sharing and building off each others knowledge to create an awesome script to easily convert UTM to Long and Lat. I have adapted the script shared on the Power BI community forum (here) by macmy034, who adapted a SQL script shared here... and so on! The key change I made to the script was to parameterise the central meridian (which is unique for each zone), so we can run the function across multiple UTM zones. The central meridian values were sources from GIS Geography, and I’d encourage you to read up on the conversion calculations if you’re interested.
👇 How to apply in your Power BI report:
The steps to applying this custom function to your Power BI file are:
Download and open the sample PBIX file, and navigate to Power Query.
Select the entire folder "UTM to Long Lat" and press CTRL + C (i.e. to copy)
Open your PBIX file (where you want to do the conversion), and navigate to Power Query.
Press CTRL + V to paste the folder into your Power Query.
Select the table which has your Eastings and Northings and Zone number in it.
Merge this table with the 'Central Meridian and UTM Zones' table based on the Zone, and expand the Central Meridian (decimal degrees) column
Select 'Add column' 'Invoke Custom Function' and name your column Longitude, and select our function. Then enter the parameters:
E: Column which contains your Eastings
N: Column which contains your Northings
Central_Meridian: Column which contains the Central Meridian (decimal degrees)
Conv: Enter text value long (make sure all lower case)
Repeat Step 7, but this time for Latitude (where Conv parameter is lat)
Change the data type to decimal
Hit close and apply and get mapping!!!
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/