Distancier/temps transport + POWER QUERY

Bonjour,

Voilà quelques semaines que j'essaie de faire une carte sur excel regroupant des carrières. J'arrive à afficher les marqueurs sur la map.

J'aimerais pouvoir renseigner l'adresse d'un chantier et que dessous s'affiche les 5 carrières les plus proches.

Je devrais donc faire appel aux services web (découverte pour moi, je patauge un peu...).

J'ai pas mal cherché sur internet mais la plupart des articles datent d'il y a quelques années et de ce que j'ai compris, la politique de google à changé depuis. Il faut maintenant une Api Key.

Cependant, je n'arrive pas à activer le complément Power Querty sur excel 2019

De plus j'aimerai que la Ville et la rue soient à indiquer dans deux cellules séparées et je n'ai pas vu comment renseigner 2 cellules par critère de recherche (arrivée et destination).

Voici mon document de base.

Bonne journée

Bonjour

Juste une précision PowerQuery est intégré à 2019 : onglet données...

Donc rien à activer...

Re,

merci, j'ai donc essayé en suivant le tuto précédemment joint... en vain

L'idéal et mon objectif est d'avoir une formule de ce type à rentrer dans ma feuille de calcul :

Distance = (villedepart, ruedepart, villedestination, ruedestination, VOTRE_API_KEY)

J'ai essayé d'adapter le code par rapport à la vidéo avec ce que je souhaite obtenir mais...

Une erreur s'est produite dans la requête « Distance ». Expression.Error : Il n'y avait pas assez d'éléments dans l'énumération pour terminer l'opération.

Détails :

Table

Pour le coup je ne pige pas très bien l'intérêt de power query ni comment obtenir ma formule directement dans mon tableur.

Et ce qui me dérange avec le Power Query (de ce que j'en ai compris) c'est de devoir obligatoirement passer par le PwQr et que ça ne se fasse pas automatiquement sur ma page principale...

A bientôt,

Re

J'ai testé les 2 solutions.

Je n'ai pas compris tes remarques à propos de PowerQuery.

Le principe, comme décrit sur le site en lien est de créer un tableau structuré avec des départs et des arrivées (ou si ton départ est fixe, on peut se limiter aux arrivées).

On bascule ce tableau dans PowerQuery

On ajoute à PowerQuery la fonction décrite.

Pour ton cas particulier où tu as rue de départ et rue d'arrivée, toujours dans PowerQuery :

  • on remplace les null par des chaînes vides dans ces 2 colonnes afin que cela ne plante pas si une information est absente
  • on ajoute une colonne utilisant la fonction et combinant rue et ville pour départ comme pour arrivée

on obtient donc un tableau avec les distances ajoutées au tableau initial (sous réserve d'inscrire le code user de API dans la requête ou de lui passer en paramètre)

On bascule ce tableau bis dans Excel.

Quand tu modifies le 1er tableau, il suffit d’actualiser tout pour que le second tableau se mettre à jour.

Le SERVICEWEB lui récupère une chaîne qu'il faut découper. Faisable mais casse pied.

Pour ma part je préfère la solution PowerQuery...

Avec un tableau nommé Data de 4 colonnes

Rue départ Ville départ Rue arrivée Ville arrivée

la fonction (où il faut remplacer le code de la clé)

let
CalculDistance = (depart, arrivee) =>

let
    Source = Xml.Document(Web.Contents(
    "https://maps.googleapis.com/maps/api/distancematrix/xml",
    [Query=[origins=depart, destinations=arrivee, mode="driving", sensor="false", key="abcdefgh"]])),
    Value = Source{0}[Value],
    Value1 = Value{3}[Value],
    Value2 = Value1{0}[Value],
    Value3 = Value2{2}[Value],
    Value4 = Value3{1}[Value]
in
    Value4
in
CalculDistance

La requête Data

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Type modifié" = Table.TransformColumnTypes(Source,{{"Rue départ", type text}, {"Ville départ", type text}, {"Rue arrivée", type text}, {"Ville arrivée", type text}}),
    #"Valeur remplacée" = Table.ReplaceValue(#"Type modifié",null,"",Replacer.ReplaceValue,{"Rue départ", "Rue arrivée"}),
    #"Fonction personnalisée appelée" = Table.AddColumn(#"Valeur remplacée", "Distance", each CalculDistance([Rue départ] &"," &[Ville départ], [Rue arrivée] & ","&[Ville arrivée]))
in
    #"Fonction personnalisée appelée"

Bonjour,

Premièrement je pense ne pas avoir très bien saisi l’intérêt de Power Query.

Par la suite il faudrait exécuter une macro pour actualiser le Power Query, quel est le bouton à aller chercher ?

Mon départ n’est pas fixe, tout comme la clé API. Tableur destiné à plusieurs personnes donc chacun son api key.

Je me demande si ma clé api fonctionne… tu pourrais envoyer ton tableur que je puisse la vérifier stp. Les api key sont toujours gratuites dans certaines limites d’utilisation c’est bien ça (200$)? et pas besoin de fournir de CB ?

Parce que j’en n’ai pas eu à saisir moi

Par contre il faudrait que le Power Query reprenne les arrivées et destinations depuis ma feuille principale.

Pas compris la fin … l’histoire des tableaux bis:/

Pour l’instant je préfère la solution service web mais je n’arrive pas non plus à la faire fonctionner

Et pour finir, où doit être mis la requête data ? elle sert uniquement à ne pas avoir d’erreur s’il manque une information, c’est bien ça ?

Mais je pense obliger la saisie de Ville et rue à chaque fois, donc pas besoin de cette partie mais avec quel sigle on indique 2 paramètres (rue & ville) pour la même inconnue (arrivée / départ) ?

Merci de ton aide,

Bonne journée.

Re

Par la suite il faudrait exécuter une macro pour actualiser le Power Query, quel est le bouton à aller chercher ?

Données, Actualiser tout

Mon départ n’est pas fixe, tout comme la clé API. Tableur destiné à plusieurs personnes donc chacun son api key.

Je me demande si ma clé api fonctionne… tu pourrais envoyer ton tableur que je puisse la vérifier stp. Les api key sont toujours gratuites dans certaines limites d’utilisation c’est bien ça (200$)? et pas besoin de fournir de CB ?

Parce que j’en n’ai pas eu à saisir moi

Dans l'exemple que j'ai décrit j'ai fait un tableau avec autant de départs que d'arrivées...

On peut partager une clé mais on risque davantage d'atteindre le seuil...

Moi j'ai du fournir une CB...

Par contre il faudrait que le Power Query reprenne les arrivées et destinations depuis ma feuille principale.

Pas compris la fin … l’histoire des tableaux bis:/

Regarde à nouveau le lien que tu as toi-même donné : à 11mn on voit la création d'un tableau dans un onglet, puis comment on l'utilise dans PowerQuery pour calculer la distance, puis à 15mn30 la récupération du résultat PowerQuery dans un autre onglet, ce que j'appelle le tableau Bis.

(Sa démo est parfois un peu laborieuse (il aurait pu épurer les erreurs) mais on arrive à suivre...)

Et pour finir, où doit être mis la requête data ? elle sert uniquement à ne pas avoir d’erreur s’il manque une information, c’est bien ça ?

Mais je pense obliger la saisie de Ville et rue à chaque fois, donc pas besoin de cette partie

NON ! La requête Data c'est ce qui transforme le tableau initial nommé Data dans le résultat qu'on récupère dans un second tableau.

Tu peux avoir des endroits en zone rurale ou industrielle où il n'y a pas de nom de rue.

Une simple ligne évite de tout planter mais tu fais comme tu veux...

Pas besoin de mon classeur :

  • crée le tableau structuré avec les 4 colonnes indiquées (en respectant la casse car PowerQuery y est sensible) et nomme-le Data
  • ajoute le tableau Data à PowerQuery
  • Crée une requête vierge avec le code que j'ai donné pour la fonction
  • modifie la valeur de la clé API
  • nomme la requête CalculDistance
  • édite ensuite la requête Data et remplace son contenu par le code que j'ai donné
  • sort par Fermer et charger dans, Table et indique ensuite l'emplacement de ce tableau.

Tu n'a plus qu'à ajouter des adresses au tableau Data et à cliquer sur Actulaliser Tout...

Bonsoir Chris,

Alors j'ai essayé en suivant bien tes étapes évoquées à la fin de ta précédente réponse mais en vain...

J'ai une erreur lorsque je rentre la requête data :

Expression.Error : Désolé... Nous n'avons pas trouvé la colonne « Rue départ » de la table.

Détails :

Rue départ

Toutes les autres étapes c'est bon

Je vais continuer comme tu le conseille avec la requête data qui empêche le plantage.

Merci pour tes précisions concernant les clés

Dans le code de la fonction CalculDistance, pouvons nous obliger à aller chercher la clé API dans ma cellule nommée "VOTRE_CLE_API" ?

Et étant donné que mes arrivées sont identiques et renseignées une seule fois en début de tableau, serait-il possible d'aller les récupérer dans une seule cellule et donc avoir un tableau DATA comportant 2 colonnes ? Du coup comme pour la clé API mais C2 (Ville arrivée) et E2 (rue arrivée)...

Merci, bonne soirée

Bonjour

Le principe, comme décrit sur le site en lien est de créer un tableau structuré avec des départs et des arrivées

Un tableau structuré n'est pas une plage nommée : ce pourquoi PowerQuery ne trouve pas les en-têtes automatiquement...

Une source PowerQuery ne doit contenir aucune ligne entièrement vide...

Les valeurs null posant problème pour les calculs notamment

J'ai ajouté la clé en variable dans la Fonction

J'ai nommé tes cellules de Rue arrivée et Ville arrivée et les ai ajouté en variables dans la requête Data.

J'ai transformé les chaînes obtenues en numérique pour pouvoir classer par ordre croissant de distance.

Remplace la clé API avant de tester...

Bonjour,

Merci pour ton tableur.

Je ne compte pas le faire, mais s'il y a plusieurs tableaux structurés présents ça ne poserait pas un problème ? Celui-ci est reconnu en tant que "Table.First" ?

Je n'ai pas réussi à le faire fonctionner. Lorsque j'actualise tout ça me demande le niveau de confidentialité puis entraîne une erreur à chaque fois...

J'ai tout essayé là : "ignorer", "public", "privé", "professionnel".

Bonne journée.

RE

Table.First c'est le 1er enregistrement de la table indiquée...

Chaque Tableau structuré ou plage nommée à un nom et on utilise le nom donc pas plus de risque qu'à la sécu avec nos numéros de sécu...

J'ai répondu Ignorer et cela a marché.

Mais peut-être as-tu mis des restriction sur ton compte google pour cette API...

Éventuellement envoie ta clé en MP...

Re

Effectivement avec ta clé cela ne passe pas.

Elle a bien la même longueur que la mienne donc je ne sais d'où cela vient.

Est-ce bien une clé sur l'API de calcul de distance Distance MATRIX API ?

Le fait que tu n'a pas eu à rentrer de CB est curieux sauf si une carte est déjà enregistrée par exemple pour les applis mobiles...

D'accord, merci d'avoir testé

Je vais donc m'y contraindre. J'ai :

Ne pas restreindre la clé

Cette clé peut appeler n'importe quelle api

Je pensais que c'était donc bon mais apparemment non xD

Non non pas de CB de rentré.

Du fait que la clé ne fonctionne pas ça te faisait des erreurs au niveau de la confidentialité aussi ?

J'essaie ça dès que j'ai un peu de temps

A bientôt (j'espère pour dire que tout vas bien )

RE

Non pas la confidentialité : je peux passer outre mais après erreur sur le calcul de distance

Bonsoir Chris

Alors alors... j'ai réussi avec mon api et le tableur a bien calculé ce que je voulais.

L'erreur pourrait être due à une erreur dans une adresse qui fait tout buguer ? "il n'y a pas assez d'éléments dans énumération pour terminer l'opération"

Ensuite il pourrait être bien d'ajouter le code postal à la ville dans les recherches pour éviter les erreurs peut être ? ou tu pense que c'est suffisant ?

Ensuite j'aimerai obtenir la durée du trajet, est-ce en ajoutant un critère "duration" dans la requête DATA ? pas de nouvelle API il me semble.

Cependant j'aimerai que cette durée soit donnée au format "00 h 00" en étant majoré d'un certain pourcentage calculé dans une cellule...

Il serait bien de pouvoir cibler le calcul à certains départements (que je fais faire par sélection sur une carte interactive). Et aussi suivant un second critère (carrière, sablière, centrale enrobé par exemple).

Les filtres n'empêchent pas le calcul ...

Par exemple si je demande les "Dépôts" ça me calcul seulement les lieux avec en Activité 1 ou 2 Dépôt... je ne sais pas si tu vois.

Est il possible de ne pas afficher certaines colonnes dans la feuille de calcul ?

Et pour terminer les liens hypertextes peuvent être cliquables dans le tableau de calcul ? j'ai pas l'impression ...

Voilà tout, j'y réfléchi de mon côté,

Bonne semaine, à +

RE

Je n'ai obtenu "il n'y a pas assez d'éléments dans énumération pour terminer l'opération" que quand l'API n'est pas bonne donc je ne sais pas si cela peut se présenter dans d'autre cas...

C'est sûr que le code postal doit pouvoir affiner la recherche, de même que le numéro de la rue et le type de voie...

Il faudrait légèrement modifier la requête à l'API ou ajouter ces éléments respectivement dans les cellule Ville départ et Rue départ.

L'API renvoie la durée mais il faut une colonne supplémentaire pour l'exploiter.

J'essaierai de regarder dans la journée...

Pour le filtre on peut filtrer avant en indiquant le choix dans une colonne ou une table à part...

On affiche les colonnes que l'on veut.

Il faudrait que tu commences à regarder le fonctionnement de PowerQuery...

Pas compris ta question à propos des hyperliens...

A noter que ton coef. PL est faux car selon le trajets tu auras une quantité différente de kms effectués à l'une ou l'autre vitesse...

Bonjour,

Je regarde tout ça la semaine prochaine.

J’ai ajouté une colonne département afin de pouvoir filtrer par la suite et j’ai deux colonnes de « matériaux » (savoir l’activité principale et secondaire de la carrière).

Il faudrait réussir à cibler la recherche à quelques départements sélectionnés avant et pour les activités aux carrières qui ont le critère de recherche dans l’une des deux colonnes « matériaux ».

Euh… mes liens hypertexte sont utilisables dans la feuille « carrières » mais dans celle « calcul » rien… J’ai seulement un texte « site » non sélectionnable.

Certes mon coef. PL n’est pas tout à fait exacte mais on ne peut pas modifier les vitesses dans google maps ?

Et puis il est quand même assez juste. Les routes qui seront le plus utilisées seront des RD, Voies expresses (et autoroutes). La moyenne des coefs est de 70%, il y aura une erreur quand il y aura beaucoup de circulation en ville mais bon… on va dire que ça prendra en compte les retards, bouchons, circulation dense…

Bon weekend à toi,

Bonjour

Je vois que le 24 j'ai complètement oublié de poster le classeur que j'avais fait

Ci-joint

Les filtres sont à faire dans PowerQuery.

Si tu as une colonne code postal, le département pourra être extrait du CP dans PowerQuery.

En revanche il faudra un tableau des départements choisis, en plus de l'activité indiquée en C6, pour croiser dans PowerQuery et limiter le résultat.

Cela nécessite de décroiser les colonnes Activité dans PowerQuery si l'activité sélectionnée peut correspondre à n'importe laquelle des 2 colonnes.

Les liens des mails et sites ne peuvent se retrouver dans le résultat d'une requête.

Quand on saisit Excel applique une action automatique mais pas quand on récupère l'info par formule ou requête.

Pour les vitesses dans google maps je te laisse chercher... dans le fichier joint j'avais utilisé ton coeff.

Rechercher des sujets similaires à "distancier temps transport power query"