Power Query Distancier entre deux villes

Staple1600 · 23 Mars 2025

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.

Cousinhub · 23 Mars 2025

Bonjour,
Hello Voisin ☔

Supposant :
Que le tableau comportant les villes se nomment "T_Ville"

Une requête (non optimisée, mais qui a le mérite de "fonctionner"?)

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"}
    }),
    F = Table.SelectRows(E, each ([Communes] = T_Ville[V1]{0} or [Communes] = T_Ville[V2]{0})),
    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,
    Dis = {Number.Round(Number.Acos(Number.Sin(lat1) * Number.Sin(lat2) + Number.Cos(lat1) * Number.Cos(lat2) * Number.Cos(long2-long1)) * 6371, 2)}
in
    Dis

Pourquoi une multiplication par 6371 😀

Peut-être?

Staple1600 · 23 Mars 2025

Bonjour @Cousinhub

Calcul de distance avec latitudes et longitudes

Nombres, curiosités, théorie et usages: calcul de la distance entre deux points de latitude et longitude données, trois méthodes

villemin.gerard.free.fr

Je teste ton code M illico presto

Cousinhub · 23 Mars 2025

Staple1600 à dit:
Bonjour @Cousinhub

Calcul de distance avec latitudes et longitudes

Nombres, curiosités, théorie et usages: calcul de la distance entre deux points de latitude et longitude données, trois méthodes

villemin.gerard.free.fr

Je teste ton code M illico presto

Et en plus, tu me demandes de lire ça un dimanche...
T'as craqué???? 🫨

Staple1600 · 23 Mars 2025

Re

@Cousinhub
J'ai une erreur sur l'étape F
Expression.Error : Le nom « T_Ville » n'a pas été reconnu. Veuillez vérifier qu'il est correctement orthographié.

Pourtant j'ai bien un tableau structuré nommé T_Ville

le rayon de la terre= 6371 km

Cousinhub · 23 Mars 2025

Hi,
Le fichier exemple

jurassic pork · 23 Mars 2025

Hello,
c'est un distancier "à vol d'oiseau" que tu veux créer ou bien avoir la distance par la route ?
Ami calmant, J.P

Staple1600 · 23 Mars 2025

Bonjour jurassic pork

Dans l'idéal par la route mais sans passer par VBA, uniquement par PowerQuery
et en utilisant le jeu de données issu de data.gouv
(sauf si tu connais d'autres sources de fichiers *.json)

@Cousinhub
Merci pour le fichier, que je teste en finissant mon apéro 😉 [yec'hed mat]

jurassic pork · 23 Mars 2025

Staple1600 à dit:
Dans l'idéal par la route mais sans passer par VBA, uniquement par PowerQuery
et en utilisant le jeu de données issu de data.gouv
(sauf si tu connais d'autres sources de fichiers *.json)

Ben moi j'ai un distancier qui utilise l' API google DistanceMatrix en VBA, mais on doit pouvoir faire cela en Power Query car il s'agit de créer une url avec les paramètres de la requête et en retour on récupère la réponse en json ou en xml.

Il y a d'autres API que celle de google pour avoir la distance entre villes car c'est assez compliqué pour avoir une clé API Google et comprendre le système de facturation mais moi je paie 0 euro par mois car je ne dépasse pas les quotas.

Staple1600 · 23 Mars 2025

Re

@jurassic pork
Ma contrainte (environnement professionnel) c'est de n'utiliser que PowerQuery (et de n'avoir aucun code VBA dans le classeur)
Et avec l'API Google, il n'y a pas une histoire de clé API ?
EDITION: Désolé, j'avais zappé la fin de ton message

@Cousinhub
Test OK de ton fichier

J'ai modifié comme suit
(mais je suppose qu'on peut produire du code M plus concis encore 😉)

PowerQuery:

let
    // Paramètres depuis Excel
    VILLES = Excel.CurrentWorkbook(){[Name="T_Ville"]}[Content],
    VilleDepart = VILLES{0}[V1],
    VilleArrivee = VILLES{0}[V2],

    // Chargement depuis data.gouv 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,
    Distance = {Number.Round(Number.Acos(Number.Sin(lat1) * Number.Sin(lat2) + Number.Cos(lat1) * Number.Cos(lat2) * Number.Cos(long2-long1)) * 6371, 2)},
    G = Table.RenameColumns(Table.FromList(Distance, Splitter.SplitByNothing()),{{"Column1", "DISTANCE (en Km)"}})
in
 G

Cousinhub · 23 Mars 2025

Staple1600 à dit:

Re

@jurassic pork
Ma contrainte (environnement professionnel) c'est de n'utiliser que PowerQuery (et de n'avoir aucun code VBA dans le classeur)
Et avec l'API Google, il n'y a pas une histoire de clé API ?

@Cousinhub
Test OK de ton fichier

J'ai modifié comme suit
(mais je suppose qu'on peut produire du code M plus concis encore 😉)

PowerQuery:

let
    // Paramètres depuis Excel
    VILLES = Excel.CurrentWorkbook(){[Name="T_Ville"]}[Content],
    VilleDepart = VILLES{0}[V1],
    VilleArrivee = VILLES{0}[V2],

    // Chargement depuis data.gouv 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,
    Distance = {Number.Round(Number.Acos(Number.Sin(lat1) * Number.Sin(lat2) + Number.Cos(lat1) * Number.Cos(lat2) * Number.Cos(long2-long1)) * 6371, 2)},
    G = Table.RenameColumns(Table.FromList(Distance, Splitter.SplitByNothing()),{{"Column1", "DISTANCE (en Km)"}})
in
 G

Re-,
Par exemple, lorsque tu "Expand", la 2ème liste contient le nom des futures colonnes. Tu peux donc les nommer directement à cette étape (pour éviter le Rename)

Staple1600 · 23 Mars 2025

Re

@Cousinhub
Donc comme ceci ?

PowerQuery:

let
    // Paramètres depuis Excel
    VILLES = Excel.CurrentWorkbook(){[Name="T_Ville"]}[Content],
    VilleDepart = VILLES{0}[V1],
    VilleArrivee = VILLES{0}[V2],

    // Chargement depuis data.gouv 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.RenameColumns(Table.ExpandListColumn(C, "dep_name"),{{"geo_point_2d.lon", "longitude"}, {"geo_point_2d.lat", "latitude"}, {"dep_name", "Département"}, {"com_name_upper", "Communes"}}),
    E = Table.SelectRows(D, each ([Communes] = VilleDepart or [Communes] = VilleArrivee)),
    lat1=E[latitude]{0}/180*Number.PI,lat2=E[latitude]{1}/180*Number.PI,long1=E[longitude]{0}/180*Number.PI,long2=E[longitude]{1}/180*Number.PI,
    Distance = {Number.Round(Number.Acos(Number.Sin(lat1) * Number.Sin(lat2) + Number.Cos(lat1) * Number.Cos(lat2) * Number.Cos(long2-long1)) * 6371, 2)},
    F = Table.RenameColumns(Table.FromList(Distance, Splitter.SplitByNothing(), null, null, ExtraValues.Error),{{"Column1", "DISTANCE (en Km)"}})
in
 F

PowerQuery:

let
    // Paramètres depuis Excel
    VILLES = Excel.CurrentWorkbook(){[Name="T_Ville"]}[Content],
    VilleDepart = VILLES{0}[V1],
    VilleArrivee = VILLES{0}[V2],

    // Chargement depuis data.gouv 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.RenameColumns(Table.ExpandListColumn(C, "dep_name"),{{"geo_point_2d.lon", "longitude"}, {"geo_point_2d.lat", "latitude"}, {"dep_name", "Département"}, {"com_name_upper", "Communes"}}),
    E = Table.SelectRows(D, each ([Communes] = VilleDepart or [Communes] = VilleArrivee)),
    lat1=E[latitude]{0}/180*Number.PI,lat2=E[latitude]{1}/180*Number.PI,long1=E[longitude]{0}/180*Number.PI,long2=E[longitude]{1}/180*Number.PI,
    Distance = {Number.Round(Number.Acos(Number.Sin(lat1) * Number.Sin(lat2) + Number.Cos(lat1) * Number.Cos(lat2) * Number.Cos(long2-long1)) * 6371, 2)},
    RESULTAT_FINAL = Table.TransformColumnTypes(Table.RenameColumns(Table.FromList(Distance, Splitter.SplitByNothing()),{{"Column1", "DISTANCE (en Km)"}}),{{"DISTANCE (en Km)", type number}})
in
RESULTAT_FINAL

Cousinhub · 23 Mars 2025

Re-,
Pas étudié finement, mais déjà pour la dernière étape de renommage, regarde le nom de la requête dans mon fichier.
Je n'ai pas transformé en Table.FromList, et le nom de la colonne est le nom de la requête... Donc peut être chargé directement à côté des villes

Staple1600 · 23 Mars 2025

Re

@Cousinhub
Justement, en regardant dans PowerQuery

je me demandais pourquoi l'icone de Distance diffère de celle de T_Ville

Si j'ai bien compris la 1er icone=List et la seconde=Table, c'est cela ?

J'ai donc voulu tout regrouper en une seule requête
(Ce n'est pas une bonne idée ?)

D'où mes posts depuis le message#10

Cousinhub · 23 Mars 2025

Re-,
Pas d'impact
Supprime la dernière étape (F), et renomme ta requête comme ceci :

Et charge-là

Effectuez une recherche sur Excel Downloads...

Power Query Distancier entre deux villes

Staple1600

XLDnaute Barbatruc

Cousinhub

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

Calcul de distance avec latitudes et longitudes

Cousinhub

XLDnaute Barbatruc

Calcul de distance avec latitudes et longitudes

Staple1600

XLDnaute Barbatruc

Cousinhub

XLDnaute Barbatruc

Pièces jointes

jurassic pork

XLDnaute Accro

Staple1600

XLDnaute Barbatruc

jurassic pork

XLDnaute Accro

Staple1600

XLDnaute Barbatruc

Cousinhub

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

Cousinhub

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

Cousinhub

XLDnaute Barbatruc

Discussions similaires

Nous accordons de l'importance à votre vie privée