Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

Power Query Distancier entre deux villes

Créer une discussion
  • Initiateur de la discussion Initiateur de la discussion Staple1600
  • Date de début Date de début

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

S'inscrire

Staple1600

XLDnaute Barbatruc
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
PowerQuery: 
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.
 
Trier par date Trier par votes
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 😉
 
Vote positif 0 Vote négatif
Re

@Cousinhub
En cherchant sur le net, j'ai trouvé cette syntaxe (étape FIN)
PowerQuery: 
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 ?
 
Vote positif 0 Vote négatif
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
VB: 
Sub temps()
t = Timer
Range("Distance").ListObject.QueryTable.Refresh False 'nom de la requête
MsgBox Timer - t
End Sub
 
Vote positif 0 Vote négatif
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)
 
Vote positif 0 Vote négatif
Re

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

PowerQuery: 
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
 
Vote positif 0 Vote négatif
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
VB: 
Sub tempsA()
t = Timer
Range("T_Ville__6").ListObject.QueryTable.Refresh False 'nom de la requête
Debug.Print Timer - t
End Sub
 
Vote positif 0 Vote négatif
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)
Cliquez pour agrandir...
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
 

Pièces jointes

Vote positif 0 Vote négatif
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
 
Vote positif 0 Vote négatif
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é)
(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)
PowerQuery: 
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.

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 😉
 
Vote positif 0 Vote négatif
Staple1600 à dit:
Dites-moi si vous constatez des effets de bord selon vos versions d'Excel.
Cliquez pour agrandir...
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 :
PowerQuery: 
(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 :
PowerQuery: 
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
 

Pièces jointes

Vote positif 0 Vote négatif
Bonsoir le fil

@jurassic pork
Du bel ouvrage, concis.
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
PowerQuery: 
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.
 
Vote positif 0 Vote négatif
Identifiez-vous ou inscrivez-vous pour participer.
Rejoins nos membres "Supporter XLD" et booste ton expérience Excel !
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

  • Question Question
Power Query Retravailler un fichier JSON avec PQ
6 7 8
Réponses
110
Affichages
12 K
Staple1600
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…