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
Bonsoir Staple1600, le forum,

En fait, 0.07:33:00 correspond à 0 jour, 7 heures, 33 minutes et 0 secondes.
Je pense qu'il faut modifier ta requête comme ça :
PowerQuery: 
...
TempsH = RéponseAPI[routes]{0}[duration] / (24 * 60 * 60),
...
Ainsi, une fois transformé en type duration, tu obtiendra 0.00:18:52, soit 18 minutes et 52 secondes pour le trajet.

Pour le formatage, je te conseille de garder la donnée en type duration dans PowerQuery et d'appliquer le format que tu veux au niveau des cellules Excel.

Bonne soirée
 
Vote positif 0 Vote négatif
Bonsoir @mromain

Merci pour ces précisions.

Puisque que tu passes par là, je vais aller tester ton classeur pour mesurer les requêtes PQ.

PS: Par curiosité et pour ma gouverne (et si tu as le temps) quelle serait ta propre syntaxe M pour faire ce distancier ?
(Histoire de remplir ma besace de requêtes PQ postées sur d'XLD par des Obiwan Kenobi de la Requête Puissante 😉 )

EDITION: @mromain
Test OK de ton fichier en changeant le typage que tu évoquais dans le message#29
 
Dernière édition:
Vote positif 0 Vote négatif
Re

@mromain
En suivant ton conseil, j'obtiens
PowerQuery: 
let
Source=...
// code M inchangé
TempsH = RéponseAPI[routes]{0}[duration] / (24 * 60 * 60),
    RESULTAT = Table.TransformColumnTypes(Table.FromRows({{DistanceKm, TempsH}},{"Distance (km)", "Durée (hh:mm:ss)"}),{{"Durée (hh:mm:ss)", type duration}})
in
RESULTAT
 
Vote positif 0 Vote négatif
Re

@jurassic pork
A partir de ton fichier, j'ai fait cette variante
J'ai ajouté cette fonction calquée sur celle que tu as faite pour calculer la distance.
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)),
        Temps = RéponseAPI[routes]{0}[duration] / (24 * 60 * 60) 
    in
        [Durée =Temps]
Et donc la requête principale
PowerQuery: 
et
    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])),
    add_Temps = Table.AddColumn(TabSrc, "Durée", each fn_Temps([lat_D],[lon_D],[lat_A],[lon_A])),
    FIN = Table.ExpandRecordColumn(Table.SelectColumns(add_Distance,{"Distance (en KM)"}), "Distance (en KM)", {"Distance", "Durée"}, {"Distance", "Durée"})
in
    FIN
 
Vote positif 0 Vote négatif
Hello,
chez moi en Excel 2021 , ton code ne fonctionne pas. Voilà le code qui fonctionne chez moi.
1 - Pour la fonction fn_Temps :
VB: 
(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)),
        Temps = RéponseAPI[routes]{0}[duration] / (24 * 60 * 60)
    in
        Temps
2 - Et pour la Requête Distance :
Code: 
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])),
    Distance_Temps = Table.AddColumn(add_Distance, "Durée", each fn_Temps([lat_D],[lon_D],[lat_A],[lon_A])),
    FIN = Table.SelectColumns(Distance_Temps,{"Distance (en KM)","Durée"})
in
    FIN

Ami calmant, J.P
 
Vote positif 0 Vote négatif
Re


@jurassic pork
Dans Excel 365, il faut que modifie le Type sinon erreur
Ce qui donne
1) DISTANCIER_JP (avec ajout du typage avec l'assistant PQ)
PowerQuery: 
let
     Source = Excel.CurrentWorkbook(){[Name="TAB_INFOS"]}[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", each fn_Distance([lat_D],[lon_D],[lat_A],[lon_A])),
    Distance_Temps = Table.AddColumn(add_Distance, "Durée", each fn_Temps([lat_D],[lon_D],[lat_A],[lon_A])),
    FIN = Table.SelectColumns(Distance_Temps,{"Distance","Durée"}),
    #"Type modifié" = Table.TransformColumnTypes(FIN,{{"Durée", type duration}, {"Distance", type number}})
in
    #"Type modifié"
2) DISTANCIER_JP (2) (avec suppression d'une étape)
PowerQuery: 
let
     Source = Excel.CurrentWorkbook(){[Name="TAB_INFOS"]}[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", each fn_Distance([lat_D],[lon_D],[lat_A],[lon_A])),
    Distance_Temps = Table.AddColumn(add_Distance, "Durée", each fn_Temps([lat_D],[lon_D],[lat_A],[lon_A])),
   FIN = Table.TransformColumnTypes(Table.SelectColumns(Distance_Temps,{"Distance","Durée"}),{{"Durée", type duration}, {"Distance", type number}})
in
    FIN
 
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…