Power Query Distancier entre deux villes

[Staple1600]

Bonjour à tous,

J'essaie de créer un distancier dans Excel en utilisant uniquement PowerQuery.

Inspiré par cette proposition d'@alexga78, je suis arrivé à ce stade

let
 Source = Json.Document(Web.Contents("https://www.data.gouv.fr/fr/datasets/r/ef85cdf0-28ed-41d1-81ee-95fbde2e6752")),
    A = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore),
    B = Table.ExpandRecordColumn(A, "Column1", {"geo_point_2d", "dep_name", "com_name_upper"}, {"Column1.geo_point_2d", "Column1.dep_name", "Column1.com_name_upper"}),
    C = Table.ExpandRecordColumn(B, "Column1.geo_point_2d", {"lon", "lat"}, {"Column1.geo_point_2d.lon", "Column1.geo_point_2d.lat"}),
    D = Table.ExpandListColumn(C, "Column1.dep_name"),
   E = Table.RenameColumns(D, {
        {"Column1.geo_point_2d.lon", "longitude"}, 
        {"Column1.geo_point_2d.lat", "latitude"}, 
        {"Column1.dep_name", "Département"}, 
        {"Column1.com_name_upper", "Communes"}
    })
in 
E

Cette requête PQ me renvoie les communes de la région Bretagne.

Je bloque sur l'implémentation du calcul de la distance en utilisant la formule de Haversine
C'est OK avec une formule Excel (voir ci-dessous)
Mais j'ai du mal à transposer cette formule dans PowerQuery

En ligne 7, ce serait le résultat à obtenir avec PQ
un tableau structuré ici nommé tbVilles qui contient les villes de départ de d'arrivée et en colonne D, un tableau structuré, résultat de la requête PQ

PS: le calcul utilisé par alexga78 dans le fil cité plus haut me donne des indices de syntaxe en code M
in Number.Round(Number.Acos(Number.Sin(lat1) * Number.Sin(lat2) + Number.Cos(lat1) * Number.Cos(lat2) * Number.Cos(long2-long1)) * 3959, 1))
mais passer des indices à une solution, c'est une autre histoire.

Merci à tous les powerqueristes qui passeront dans le fil pour éclairer ma lanterne 😉

Bon dimanche printanier.

 

[Staple1600]

Re

[APARTE]
En creusant un peu, dois-je me convaincre que Microsoft n'aime que l'Amérique ?

C'est pas mal cette fonctionnalité Données géographiques dans 365
Sauf qu'un code postal ne vaut pas un zip code!!!

Ca n'a pas été localisé pour être utilisé partout dans le monde ?

PS: Dans cet exemple , j'ai pris un autre jeu de données *.json (le même que celui utilisé par @alexga78 dans le fil cité dans mon 1er message)
Source = Json.Document(Web.Contents("https://www.data.gouv.fr/fr/datasets/r/521fe6f9-0f7f-4684-bb3f-7d3d88c581bb")),
[/APARTE]

@Cousinhub
Merci pour la coup de main 😉

 

[Staple1600]

Re

@Cousinhub
En cherchant sur le net, j'ai trouvé cette syntaxe (étape FIN)

Spoiler: code M masqué pour gain de place

let
    // Paramètres depuis Excel
    VILLES = Excel.CurrentWorkbook(){[Name="T_Ville"]}[Content],
    VilleDepart = VILLES{0}[V1],
    VilleArrivee = VILLES{0}[V2],
    // Chargement et transformation des données
    Source = Json.Document(Web.Contents("https://www.data.gouv.fr/fr/datasets/r/ef85cdf0-28ed-41d1-81ee-95fbde2e6752")),
    A = Table.FromRecords(Source, {"geo_point_2d", "dep_name", "com_name_upper"}),
    B = Table.TransformColumns(A, {{"geo_point_2d", each [lon = _[lon], lat = _[lat]], type record},{"com_name_upper", each _, type text}}),
    C = Table.ExpandRecordColumn(B, "geo_point_2d", {"lon", "lat"}, {"geo_point_2d.lon", "geo_point_2d.lat"}),
    D = Table.ExpandListColumn(C, "dep_name"),
    E = Table.RenameColumns(D,{{"geo_point_2d.lon", "longitude"}, {"geo_point_2d.lat", "latitude"}, {"dep_name", "Département"}, {"com_name_upper", "Communes"}}),
    F = Table.SelectRows(E, each ([Communes] = VilleDepart or [Communes] = VilleArrivee)),
    lat1=F[latitude]{0}/180*Number.PI,lat2=F[latitude]{1}/180*Number.PI,long1=F[longitude]{0}/180*Number.PI,long2=F[longitude]{1}/180*Number.PI,
    FIN = #table({"Distance (en KM)"},{{Number.Round(Number.Acos(Number.Sin(lat1) * Number.Sin(lat2) + Number.Cos(lat1) * Number.Cos(lat2) * Number.Cos(long2-long1)) * 6371, 2)}})
in
    FIN

Question subsidiaire
Comment faire (comme on peut le faire en VBA) pour comparer la "vélocité" d'un code M par rapport à un autre ?
Comment savoir entre deux requêtes PQ laquelle s'exécute le plus rapidement ?

 

[Cousinhub]

Re-,
Si j'ai bien compris
Perso, j'utilise ce code pour mesurer le temps d'une requête
False, pour être sûr de ne pas actualiser la requête en arrière-plan

Sub temps()
t = Timer
Range("Distance").ListObject.QueryTable.Refresh False 'nom de la requête
MsgBox Timer - t
End Sub

 

[Cousinhub]

Re-,
Si j'ai bien compris
Perso, j'utilise ce code pour mesurer le temps d'une requête
False, pour être sûr de ne pas actualiser la requête en arrière-plan

Sub temps()
t = Timer
Range("Distance").ListObject.QueryTable.Refresh False 'nom de la requête
MsgBox Timer - t
End Sub

Re-bis
Attention, le temps peut évoluer (du simple au double) selon que c'est la première connexion, ou une mise à jour de la requête (connexion déjà effective)

 

[Staple1600]

Re

@Cousinhub
Merci pour ce petit bout de code
Temps obtenu
Distance
3,8125
Code M (message#17)
2,484375

Spoiler: Et avec ce code M (le dernier en date mon PQ) -> 2.40625

let
    // Paramètres depuis Excel
    VILLES = Excel.CurrentWorkbook(){[Name="T_Ville"]}[Content],
    VilleDepart = VILLES{0}[V1],
    VilleArrivee = VILLES{0}[V2],
    // Chargement et transformation des données
    Source = Json.Document(Web.Contents("https://www.data.gouv.fr/fr/datasets/r/ef85cdf0-28ed-41d1-81ee-95fbde2e6752")),
    SourceFiltrée = List.Select(Source, each _[com_name_upper] = VilleDepart or _[com_name_upper] = VilleArrivee),
    A = Table.FromRecords(SourceFiltrée, {"geo_point_2d", "dep_name", "com_name_upper"}),
    B = Table.ExpandRecordColumn(A, "geo_point_2d", {"lon", "lat"}, {"geo_point_2d.lon", "geo_point_2d.lat"}),
    C = Table.ExpandListColumn(B, "dep_name"),
    D = Table.RenameColumns(C,{{"geo_point_2d.lon", "longitude"}, {"geo_point_2d.lat", "latitude"}, {"dep_name", "Département"}, {"com_name_upper", "Communes"}}),
    lat1=D[latitude]{0}/180*Number.PI,lat2=D[latitude]{1}/180*Number.PI,long1=D[longitude]{0}/180*Number.PI,long2=D[longitude]{1}/180*Number.PI,
    FIN = #table({"Distance (en KM)"},{{Number.Round(Number.Acos(Number.Sin(lat1) * Number.Sin(lat2) + Number.Cos(lat1) * Number.Cos(lat2) * Number.Cos(long2-long1)) * 6371, 2)}})
in
    FIN

 

[Cousinhub]

Re-,
Tu effectues bien tes tests à l'ouverture du fichier?
Tu modifies le code M, enregistre, ferme, puis ouvre à nouveau, et "Clic"

 

[Staple1600]

Re

@Cousinhub
Non, j'ai dupliqué les requêtes, puis modifier le code M et enfin renommer la requête
et depuis VBE, j'ai exécuté N fois ton code VBA en changeant le nom de la requête
Exemple pour mon dernier test

Sub tempsA()
t = Timer
Range("T_Ville__6").ListObject.QueryTable.Refresh False 'nom de la requête
Debug.Print Timer - t
End Sub

 

[mromain]

Bonjour Staple, Cousinhub, jurassic pork, le forum,

Je viens sur ce fil concernant le calcul de performance des requêtes PowerQuery.

Comme le dit @Cousinhub :

Attention, le temps peut évoluer (du simple au double) selon que c'est la première connexion, ou une mise à jour de la requête (connexion déjà effective)

C'est vrai !
J'ai remarqué que lors de la première ouverture de l'éditeur PowerQuery ou de la première exécution d'une requête sur l'instance d'Excel, une bonne partie du temps mis réside dans le chargement du .NET Framework utile à PowerQuery (on peut le voir dans la statusbar d'Excel).

Sur mon PC, la première ouverture de l'éditeur PowerQuery prend environ 4,5 secondes alors que les suivantes ne prennent qu'une seule seconde.

Sinon, pour les calculs de durée d'exécution des requêtes, j'utilisais la même méthode que @Cousinhub.
À force de réécrire ce code, je me suis fait un petit fichier dédié qui est épinglé dans mes favoris.
Lorsque je veux calculer la durée d'exécution des requêtes d'un classeur, il me suffit de l'ouvrir et de cliquer sur le bouton.
Il permet de rafraîchir les requêtes renvoyées dans des tableaux d'un classeur. Il logue les durées d'exécution de celles-ci et présente dans un TCD les durées Min, Max et Moyenne de chaque requête.

On sait jamais, si ça peut servir...

Bonne journée

 

[jurassic pork]

Hello,
Staple1600 si tu es intéressé par avoir la distance par la route entre deux points géolocalisés en utilisant powerquery en passant par un service web va voir cette discussion.
Le service openrouteservice est gratuit et c'est facile pour avoir une clé. Si tu n'as pas trop de requêtes à faire cela peut suffire :
limites : 40 requêtes par minute , 1000 requêtes par jour.
avec ce service on peut aussi avoir les coordonnées d'une adresse et la route empruntée entre deux points.
Ami calmant, J.P

 

[Staple1600]

Bonjour le fil, @mromain

@mromain
Merci pour ton fichier

@jurassic pork
Je suis en test avec un mix de formules (SERVICEWEB / FILTRE.XML) + PQ avec une API Open Source (qui ne nécessite pas de clé)
J'ai un souci entre les versions de PQ sur 2019 et 365 ( apparemment il y a des différences)

 

[Staple1600]

Re

Voici la formule qui me permet de m'affranchir du fichier *.json issu de data.gouv.fr
=FILTRE.XML(SERVICEWEB("https://nominatim.openstreetmap.org/search?q="&URLENCODAGE($A$2&",France")&"&format=xml");"//searchresults/place[1]/@lat")
Avec en A2 => Rennes

Je vous laisse adapter les formules en C2,E2 et F2 (pour la longitude, c'est @lon , mais cela vous l'avez deviné, non ? 😉

Lors de mes tests, j'ai constaté des choses bizarres (Excel se ferme sans prévenir, et tous les classeurs ouverts sont fermés brutalement)

Ensuite, j'utilise cette API* (qui ne nécessite pas de clé)

OSRM API Documentation

project-osrm.org

(je me suis usé les yeux pour comprendre la syntaxe, heureusement que G..gle m'a aidé, ainsi quelques sites dédiés à .Power BI

Voici le code M auquel je suis arrivé dans Excel 365
(Il faudra que je teste demain sur Excel 2019, car avec cette version , j'ai eu beaucoup d'erreur de syntaxe en code M lors de mes précédents essais)

let
Source = Excel.CurrentWorkbook(){[Name="TAB_INFOS"]}[Content],
aLat = Text.From(Source{0}[lat_D]),
aLon = Text.From(Source{0}[lon_D]),
bLat = Text.From(Source{0}[lat_A]),
bLon = Text.From(Source{0}[lon_A]),
U_R_L = "http://router.project-osrm.org/route/v1/driving/" & aLon & "," & aLat & ";" & bLon & "," & bLat & "?overview=false",
RéponseAPI = Json.Document(Web.Contents(U_R_L)),
DistanceKilometres = RéponseAPI[routes]{0}[distance] / 1000,
RESULTAT= #table({"Distance (en KM)"}, {{DistanceKilometres}})
in
RESULTAT

Dites-moi si vous constatez des effets de bord selon vos versions d'Excel.

Spoiler: Parallèlement, je suis une autre piste ...

pour ce qui est d'un distancier pour les seules communes de la Région Bretagne
(téléchargement des 1200 communes dans une feuille Excel puis geocodage de ce fichier (merci la Poste)
J'obtiens alors un fichier csv avec des lat et long.
Mais ceci est une autre histoire, sur laquelle je reviendrai plus tard 😉

 

[Staple1600]

Re

@mromain
J'ai une erreur ici:
rStart = GetTickCount

=> Incompatibilité de type

 

[jurassic pork]

Dites-moi si vous constatez des effets de bord selon vos versions d'Excel.

Hello,
Staple1600, j'ai un peu modifié tes formules et ton code PowerQuery
1 - Pour les formules , pour éviter d'appeler le service web 2 fois par ville, j'utilise une colonne supplémentaire ou j'appelle le service web,et je viens piocher dedans pour récupérer la longitude et la latitude de la ville.
2 - Comme ton service web comprend aussi les adresses , je mets des adresses plutôt que des villes, comme cela le calcul de distance est plus précis (surtout pour les grandes villes)
3 - Pour le PowerQuery , j'ai créé une fonction personnalisée (fn_Distance) qui renvoie la distance en fonction des 4 coordonnées :

(lat_D as text,lon_D as text, lat_A as text, lon_A as text) =>
    let U_R_L = "http://router.project-osrm.org/route/v1/driving/" &  lon_D &
            "," & lat_D & ";" & lon_A  &  "," & lat_A & "?overview=false",
    RéponseAPI = Json.Document(Web.Contents(U_R_L)),
    DistanceKilometres = RéponseAPI[routes]{0}[distance] / 1000
in
    DistanceKilometres

et la requête powerQuery devient :

let
    Source = Excel.CurrentWorkbook(){[Name="Tableau1"]}[Content],
    #"TabSrc" = Table.TransformColumnTypes(Source,{{"Adresse_D", type text}, {"res_D", type text},
                                                   {"lat_D", type text}, {"lon_D", type text},
                                                   {"Adresse_A", type text}, {"res_A", type text},
                                                   {"lat_A", type text}, {"lon_A", type text}}),
    add_Distance = Table.AddColumn(TabSrc, "Distance (en KM)", each fn_Distance([lat_D],[lon_D],[lat_A],[lon_A])),
    RESULTAT = Table.SelectColumns(add_Distance,{"Distance (en KM)"})
in
    RESULTAT

Voici le résultat (classeur en pièce jointe) :

testé OK avec Excel 2016 et Excel 2021

Ami calmant, J.P

 

[mromain]

Re

@mromain
J'ai une erreur ici:
rStart = GetTickCount

=> Incompatibilité de type

Bonjour Staple1600, le forum,

Tu dois surement être sur une version 64 bits.
Tu peux tester en enlevant le typage de rStart et rEnd ainsi :

Dim rStart
Dim rEnd

Malheureusement, je ne peux pas tester pour assurer que ça fonctionnera...

Bonne journée

 

[Staple1600]

Bonsoir le fil

@jurassic pork
Du bel ouvrage, concis.

Spoiler: j'irais jusqu'a...

dire: joli et ciselé 😉

En lisant la doc de l'API OSRM, j'ai lu ceci
duration: The estimated travel time, in float number of seconds.
et donc tenté cela

let
    Source = Excel.CurrentWorkbook(){[Name="TAB_INFOS"]}[Content],
    aLat = Text.From(Source{0}[lat_D]),
    aLon = Text.From(Source{0}[lon_D]),
    bLat = Text.From(Source{0}[lat_A]),
    bLon = Text.From(Source{0}[lon_A]),t
    U_R_L = "http://router.project-osrm.org/route/v1/driving/" & aLon & "," & aLat & ";" & bLon & "," & bLat & "?overview=false",
    RéponseAPI = Json.Document(Web.Contents(U_R_L)),
    DistanceKm = RéponseAPI[routes]{0}[distance] / 1000,
    TempsH = RéponseAPI[routes]{0}[duration] / 3600,
    RESULTAT = Table.FromRows({{DistanceKm, TempsH}},{"Distance (km)", "Durée"})
in
    RESULTAT

Si je teste avec Rennes / Betton, j'obtiens12,2204 km et 0,31458333333333333 en durée
Et si j'ajoute ceci
#"Type modifié" = Table.TransformColumnTypes(RESULTAT,{{"Durée", type duration}})
J'obtiens : 0.07:33:00
Donc format Excel => j.hh:mm:ss

Ce qui fait donc 7 minutes 33 secondes pour faire 12,22 Km, non ?

Mais comment formater ce résultat pour avoir 7m33s

@mromain
Je vais faire les modifs et tester.