Hi !
I'm using the MapQuest API that calculates the optimized route given a starting location and several served locations.
For each trip, given the different locations, I want to have the distance of the trip.
I saw how to calculate a distance between TWO locations with a chosen API, it works as follows :
(in power query)
1) create a function with the API
2) add a column in a table where there is a column for origins and a column for destinations, these columns are used as parameters of your function
3) expand the added column
⇒ it gives you several new columns with the distance, the duration, ... and you can select information you want
(https://www.youtube.com/watch?v=ZVE3POxiRKs)
I need to do something like that but my problem is that instead of having 2 location parameters (origin and destination), I have several location parameters (start location and several served locations) and the number of locations changes according to the number of locations visited during the trip.
I don't see how I can put the locations in different columns since there is a different number of location for each trip, and originally they are stored in different rows.
| TRIP_ID | START_LOCATION | SERVED_LOCATION |
| 1 | Denver | WestMinster |
| 1 | Denver | Lakewood |
| 1 | Denver | Boulder |
| 2 | New York City | Boston |
| 2 | New York City | Pittsburgh |
| 3 | Santa Fe | Salt Lake City |
| 3 | Santa Fe | Seattle |
| 3 | Santa Fe | Sacramento |
Here is an example of the API code to obtain information about trip 1:
https://www.mapquestapi.com/directions/v2/optimizedRoute?json={"locations":["Denver, CO","WestMinster, CO","Lakewood, CO","Boulder, CO"]}&outFormat=json&key=KEY
⇒ I can add as many locations as I want
I don't know how to adapt the process to my case, would you have any idea ? I'm really stuck here.
Thank you very much for your help !
