A little while ago, a client came through with a list of 400 addresses that they wanted displayed in a map visual in Power BI based on specific criteria. Unfortunately, in order to display the addresses in the way that the client wanted the map visual required us to get the longitude and latitude for each address. We didn’t want to manually search every address to figure out their specific data so we built a workflow to get longitude and latitude via Power Automate.
Step 1: Build the SharePoint Database
We chose to build our database out via a SharePoint list. SharePoint provides an easy to access data source for Office 365 tools like Power Automate and has the added benefit of being included with most forms of licensing. We chose to implement the following columns to support our Power BI Report:
- Company Name
- Company Type
- Address
- City
- State
- Zip
- Company Website
- Latitude
- Longitude
Note: Make sure you include a column to hold your generated latitude data as well as your longitude data.
Step 2: Build the Power Automate Workflow
At this point we have everything we need in place to get started building out the workflow. Start off the workflow with a SharePoint trigger of “When an Item is Created or Modified” and pass in the URL of the list you just built:
The next action is where all the magic happens. Bing provides us an API that we can call to pull back latitude and longitude data. Add the Bing action called “Get Location by address” and pass in the address parameters from the SharePoint list.
Running the workflow at this point would show us that the Bing action returns us the latitude and longitude data that we are expecting. Now we just have to input it back into the SharePoint list we created. You can do this by adding a SharePoint action called “Update Item” and passing in the ID of the triggered value as well as the generated longitude and latitude data.
You’re done! The full workflow is pictured below for reference, but at this point we have a workflow that will go out and get us the latitude and longitude data we need every time we enter a new address in our SharePoint list.
It’s a very simple workflow to take care of a task that would have taken a painful amount of hours otherwise. For more “How To” guides with Power Automate, check out the “How To” section on our blog.
For more information on Power Automate check out Microsoft’s documentation here.
0 Comments