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
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
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
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
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
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
It works great.
I would never had known how to do this by myself. Thank you very much for all your help !
Regards,
Marion