Forum Discussion
distance calculation : optimized route mapquest api
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
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
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 :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 !
MarionThe 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
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