What Three Words API

[PaulBoyes]

I would like to use the What Three Words app to create the GPS longitude and latitude coordinates for specific location of various assets around the globe.

The What Three Words app has an API and I wonder if that can be used from within Power BI to automatically generate the coordinates.

Thanks

Paul

[BrianJ]

@PaulBoyes ,

I’m not sure whether this can be done in Power BI alone, but there is an R package developed specifically to handle this task. As you’ll see from the examples in the document below, the R scripts to do this are very simple and can be called from within a Power Query step:

threewords.pdf

I hope this is helpful.

– Brian

[tweinzapfel]

@PaulBoyes, @BrianJ - this concept is something that I’m actively looking at as I’m trying to do something similar (do an API call to obtain data and then join with other data in PBI).

I agree with Brian on executing an R script to do the call (and in my case, I’m doing one using a POST request and then just embedded the header information needed for the request in the R script).

One other possible approach is that you can do API calls through Power Automate. I’m currently using this approach also to pull data from an API using a POST request. And - you can also set up initial variables in your flow to enter in data used for your request. Then it should be possible to save the resulting data - perhaps in a file connected to an existing PBI report - and then refresh the PBI report (all in the same flow). (Side note - Henry Habib has a fantastic series on Power Automate in the EDNA Learning Center)

But I’m in the process of doing something similar so will want to see what ideas others have.

[BrianJ]

@PaulBoyes ,

Great suggestions from @tweinzapfel to also look at Power Automate as an option here.

In addition, in the writeup that the JMAP team did for Challenge #12, I provided a detailed section on how I called the Opencage API from an R script in PQ to obtain Lat/Long coordinates for the airports in the dataset. Obviously, a different API, but same general approach I think would work well here.

Power BI Challenge 12 - Transport & Shipping Data from Greg, Brian, Jarrett, and Mudassir

Collaboration: To help document the steps each team member took during collaborative development efforts, a hidden [NOTES] page was created for each user and was updated as work progressed. We were still challenged (no pun intended) by the difficulty in sharing a single PBIX file among multiple developers, and worked serially with each developer updating the version number in the file name and appending their initials to make sure we didn’t overwrite others’ work. Still an issue with collabora…

Note: I wasn’t familiar with What Three Words before readiing your initial post, but that’s super cool.

Also looping in our resident geospatial wizard @Paul here for his advice/input.

And if you’re interested in doing this via Python call from PQ instead of R, we can check with @gmh1977 , @gjmount and/or @AntrikshSharma.

Amost too many options here…

• Brian

[BrianJ]

@PaulBoyes ,

One more suggestion – Microsoft MVP Luca Zavarella wrote a fantastic book last year called “Extending Power BI with Python and R”. He has an entire chapter entitled “Calling External APIs to Enrich Your Data”. If you’re at all interested in using Python or R in conjunction with Power BI, I highly recommend it.

– Brian

[Paul]

Hi,
With the API you can get the coordinates, but also the 3m x3m grid, which would be great to display in a map visual as well. You can use Mapbox for this, or WKT in Iconmap.

I have not looked at w3w for a while now, lacking a real life use case, but it could be interesting to display assets in “no address” locations.

Interested in your choices here.

Paul

[PaulBoyes]

Thank you to all for your very helpful suggestions.

Basically what is happening is that we have developed an IOT device that, amongst other things, measures and monitors machines & equipment performance using a variety of sensors (Power, temperature, vibration, gas analyses (for around 10 gases) and particulates).

The data is captured in real time, transmitted to a web based SQL database, then email / SMS alerts are sent in real time for critical faults.

We then use Power BI to provide the analytics.

The problem is that the assets we are monitoring are not necessarily at a particular postal address so being able to identify a 3m square location would be very helpful for engineers responsible for servicing etc.

[Paul]

Paul,
Great application for W3W.
I recall a project in the UK, where we had to arrange for delivery of spare parts to “intelligent” broadcasting masts, that would indicate a technical failure, specifying the spare part that needed replacement. These masts were all over the place in the rough. From the mast a message was sent to a service agent in Hungary, where the parts were stored, order picked, shipped overnight and DHL would deliver at the coordinates derived from the W3W. (centroid)

You may want to use QGIS for the conversion of the grid to WKT (or a Shape file) or W3W to coordinates.

Paul