How to call WEB API iteratively using values from Excel or power Query column

[pbi_user2024]

Hi,

Trying to get distance between 2 points, either Zipcodes or Latitude/Logitude. Got the API key for Google Distance matrix API. Works fine when I manually try. But I have an Excel file in which I have columns with Zip codes for from and To and I want to iteratively go thru that list and get the distance and add in a new column. Either in excel or on Power Query.

For e,g, I tried below in a browser and got the distance.

https://maps.googleapis.com/maps/api/distancematrix/json
?destinations=22434
&origins=23173
&units=imperial
&key= XXXX

Anyone has any ideas ? A step by step guide will be very helpful.

Thanks in advance.

Ann

[johnbasha33 · Accepted solution]

@pbi_user2024 

Hi try the below.
=Json.Document(Web.Contents("https://maps.googleapis.com/maps/api/distancematrix/json?origins=" & Text.From([FromZip]) & "&destinations=" & Text.From([ToZip]) & "&units=imperial&key=YOUR_API_KEY"))[rows]{0}[elements]{0}[distance][text]

1. Open your Excel file containing the columns with zip codes for "from" and "to".
2. Go to the "Data" tab and click on "From Table/Range" to import the data into Power Query.
3. In the Power Query Editor, select the columns containing the "from" and "to" zip codes.
4. Go to the "Add Column" tab and click on "Custom Column".
5. In the "Custom Column" dialog box, enter a name for the new column (e.g., "Distance").
6. In the custom column formula, construct the URL to call the Google Distance Matrix API. You can use the Text.From function to convert the zip codes to text and concatenate them into the URL. Here's an example formula:
   Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!