Google Maps Distance Function Help

[hatahetahmad]

I used this function to get duration and distance between two GEO code from "Curbal Youtube Channel"

I thanks Ruth for this amazing function

this is it after littel editing:

(Origins as text, Destinations as text)=>
let
Source = Json.Document(Web.Contents("https://maps.googleapis.com/maps/api/distancematrix/json?units=metric&origins="&Origins&"&destinations="&Destinations&"&mode=driving&key="&GMAPIKey)),
rows = Source[rows],
#"Converted to Table" = Table.FromList(rows, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"elements"}, {"elements"}),
#"Expanded elements" = Table.ExpandListColumn(#"Expanded Column1", "elements"),
#"Expanded elements1" = Table.ExpandRecordColumn(#"Expanded elements", "elements", {"distance", "duration"}, {"distance", "duration"}),
#"Expanded distance" = Table.ExpandRecordColumn(#"Expanded elements1", "distance", {"text"}, {"text"}),
#"Expanded duration" = Table.ExpandRecordColumn(#"Expanded distance", "duration", {"text"}, {"text.1"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded duration",{{"text", "Distance"}, {"text.1", "Duration"}})
in
#"Renamed Columns"

after scrolling the first 1000 rows I got an error (the Column1 of the table wasn't Found)

I know that Google Maps Limit is 25K request per day and I Only used 2,7K a lot less than the free plan offer so I think it can't be the problem

I have another question:

When I Invoke the function It makes 13000 request beacuse i have 13K rows, Google maps Get 1000 request beacuse query limit and after I apply it to Power BI Desktop it'll make ?

1000 for the query + 13000 my rows = 14000 request

or the first 1000 already been stored then start getting data for th other rows

1000 + 12000 my rows = 13000 total

which one is correct so i can calculate and limit my data in future.

thank you.

[v-huizhn-msft · Accepted solution]

Hi @hatahetahmad,

After research, you can use a tool like Fiddler to trace your HTTP traffic and see what the detailed message is. And I am not specific about Power Query statement, and I am not sure which one is correct, I personally suggest you post the case to right forum: Power Query forum to get dedicated support. Hope the following threads or article are useful.

Waiting Between Web Service Requests In Power Query
Google Places API and Power Query Function

Engine/IO/Web/Request/GetResponse and Rest API rate limits

Best Regards,
Angelia

[hatahetahmad]

Thank You @v-huizhn-msft

I'll try to find the error using this tools

many thanks

[v-huizhn-msft]

Hi @hatahetahmad,

You're welcome, it's my pleasure to help you. Please mark the helpful reply as answer, so more people will benefit from here.

Best Regards,
Angelia