Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

distance calculation : optimized route mapquest api

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_IDSTART_LOCATIONSERVED_LOCATION
1DenverWestMinster
1DenverLakewood
1DenverBoulder
2New York CityBoston
2New York CityPittsburgh
3Santa FeSalt Lake City
3Santa FeSeattle
3Santa FeSacramento

 

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 !

 

1 ACCEPTED SOLUTION

Here is some additional instruction

1. Starting with your existing query, group it on the Trip_ID and Start_Location columns, using the Group By button in the ribbon.  Call the column "Locations".  Choose All Rows as the aggregation.

2.  Add a custom column called "ListOfLocations" with this formula -    = List.Combine({{[START_LOCATION]}, [Locations][SERVED_LOCATION]}) 

3.  Add a custom column called "ListInQuotes" with this formula -    = Text.Combine(List.Transform([ListOfLocations], each """"&_&""""), ", ")

4. Concatenate that new column with the rest of your web call to get your results on each row/for each Trip_ID

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

12 REPLIES 12
mahoneypat
Employee
Employee

Your data doesn't have a State code column.  Assuming you can add that and concatenate it with your city, here is an example of how you can then make the text string you need for your API call.  This will make a text string of your starting city, and all the other cities on the trip, each surrounded in double quotes.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXJJzStLLQIywlOLS3wz84pLgLxYHTRJn8Ts1PL8/BRMGaf80pwUqBYjIN8vtVwhMr8oW8E5s6QSLF9ckp+HUzogs6SkOKm0KD0DrMQYKBScmFeSqOCWCmbmlCiA7IYox6YiNbGkJCcVu+bkosTc1LySfKXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TRIP_ID = _t, START_LOCATION = _t, SERVED_LOCATION = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"TRIP_ID", Int64.Type}, {"START_LOCATION", type text}, {"SERVED_LOCATION", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"TRIP_ID", "START_LOCATION"}, {{"Locations", each _, type table [TRIP_ID=number, START_LOCATION=text, SERVED_LOCATION=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "ListOfLocations", each List.Combine({{[START_LOCATION]}, [Locations][SERVED_LOCATION]})),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "ListInQuotes", each Text.Combine(List.Transform([ListOfLocations], each """"&_&""""), ", "))
in
    #"Added Custom1"

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

Hi @mahoneypat !

Thank you very much for your answer.
It works perfectly well displaying the data of the table I put in my first message, but I created this table with fake data just to show the structure of the real table. In fact my real table is far bigger and looks like this :

MarionL_0-1594375855604.png

I'm sorry I misled you. Is it still possible to make it work and access the data of my real table ?
Thank you very much !

Marion

The image is too blurry to use OCR to pull out the data to test it out.  Can you share data copy/pasted from Excel, so I can put it into a query?  In any case, from what I can see, it looks very similar to your example data (and already includes the State?).  Did you try the query approach I suggested with your actual data?

 

FYI that I may not respond again until Monday.

 

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

Hi @mahoneypat,
Actually, it would be great in my case if I could pull out the data directly from my table because the data is evolving and I want to be able to actualize without changing the code (but if it's too complicated, having to do it manually is still better than nothing).
Yes, the real data has a structure very similar to the fake one and already includes the State.
I was able to try the query approach with the steps you described, it worked and gave me the fake data, but I don't know how to adapt the query approach to have my real data.

Thank you very much !


Best regards,
Marion

I wasn't proposing to use OCR as part of the solution.  I was just looking to get some example data that matches your real data.  Sometimes OCR can be used to extract the data from an image someone provides.  In your case, the image was too blurry to do that.  Please provide more representative data (either by inserting a table into your reply, or giving a link to an excel file, etc.  I will then adapt the M code with the new data.

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

Hi @mahoneypat ,
I not sure I understood what you need to modify your M code. If I sent you a small part of my actual data, would you be able to modify your M code so it would give me what I need for my entire data ?
If not, what do you need exactly ?
Thank you,

Regards,
Marion

Since your real data has the same column names as your mock data, the M code I provided should actually work.  However, I just showed how to turn city names in the column to a text string of names in quotes.  You should just need to concatenate that text string with the rest of the URL for the the API call to get it to work.  Does that make sense?  Have you tried the code I provided on your actual data to see if it makes the text string segment you'll need?

 

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

@mahoneypat,
"However, I just showed how to turn city names in the column to a text string of names in quotes." => yes, it's perfect, that's exactly what I need.
I tried the M code you provided but it gave me text string segments of the mock data from my first post and not of my actual data, even though the column's names are the same. Are you sure that it should work ? Could you modify it so it would work and give me my actual data ?
Thank you,

Regards,
Marion

You will need to adapt your real query with the approach I showed in the example.  Starting with the #"Grouped Rows" step forward.  Once you get the text string of cities, you'll then need to concatenate that string into the longer URL web call to return your results.

Regards,

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

Hi @mahoneypat,


What do I have to do to adapt the code you wrote to my real query ? I don't know how to do it.
What do you mean by "Starting with the #"Grouped Rows" step forward" ?

Thank you,

 

Regards,

Marion

Here is some additional instruction

1. Starting with your existing query, group it on the Trip_ID and Start_Location columns, using the Group By button in the ribbon.  Call the column "Locations".  Choose All Rows as the aggregation.

2.  Add a custom column called "ListOfLocations" with this formula -    = List.Combine({{[START_LOCATION]}, [Locations][SERVED_LOCATION]}) 

3.  Add a custom column called "ListInQuotes" with this formula -    = Text.Combine(List.Transform([ListOfLocations], each """"&_&""""), ", ")

4. Concatenate that new column with the rest of your web call to get your results on each row/for each Trip_ID

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

It works great.
I would never had known how to do this by myself. Thank you very much for all your help !
Regards,
Marion

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors