Let’s say you have a SharePoint list storing information with an Eircode column (postal code for addresses in Ireland), and you want to use that information in Power BI later to generate a map organizing all your items by location. Unfortunately, the Power BI maps don’t work fine with Eircodes, so how could we get the locations with the most precise information?
Let’s use a Flow and Google Maps API for that!
(Sorry Microsoft for not using Bing Maps)
And include two new columns of type number and automatic decimal places in the SharePoint list: Latitude and Longitude
With those two things set up, it’s time to begin the Flow creation.
We will use the SharePoint ‘When an item is Created or Modified’ Trigger to start our flow. After starting the Flow creation with this trigger, add an ‘HTTP’ action. To get the Latitude and Longitude information, make a get Request to Google Maps API using the Eircode coming from the SharePoint item as Address filter:
In this case, the search is filtered to be just in Ireland as you can see in the parameters.
The results we get from Google Maps API are in the following JSON structure:
Note we have the latitude and longitude under geometry/location. So, to manage that information easily, create two variables ‘Latitude’ and ‘Longitude’, of type Float.
Assign the value coming from the HTTP request body to the variables using an expression for each (which will navigate through the JSON object to access the data):
Now that you have your values properly assigned, check if one of them differ from the existing ones in SharePoint, in case it is different it means the item needs to be updated. Then add an ‘Update List Item’ Action and set it to update your list items with the new Latitude and Longitude values to finish.
The final layout of the flow will be the following:
Now, as soon as your items are updated or created in the SharePoint list, the information will be ready to be used in the Map visual on Power BI with the SharePoint list as data source.