Forum Discussion
Solved
How to call WEB API iteratively using values from Excel or power Query column
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...
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]- Open your Excel file containing the columns with zip codes for "from" and "to".
- Go to the "Data" tab and click on "From Table/Range" to import the data into Power Query.
- In the Power Query Editor, select the columns containing the "from" and "to" zip codes.
- Go to the "Add Column" tab and click on "Custom Column".
- In the "Custom Column" dialog box, enter a name for the new column (e.g., "Distance").
- 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 !!
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]
- Open your Excel file containing the columns with zip codes for "from" and "to".
- Go to the "Data" tab and click on "From Table/Range" to import the data into Power Query.
- In the Power Query Editor, select the columns containing the "from" and "to" zip codes.
- Go to the "Add Column" tab and click on "Custom Column".
- In the "Custom Column" dialog box, enter a name for the new column (e.g., "Distance").
- 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 !!