Utilisation d'un filtre VBA sur Power Query

Bonjour,

J'ai un CSV contenant des informations touristiques (plateforme datatouriste): nom, longitude, latitude, adresse, information, etc. Ce CSV fait ~350000 lignes, donc difficile à mettre dans un excel sans que cela crash.
Mon objectif est le suivant: J'ai une position GPS, je souhaite récupérer tous les évènements touristiques dans un rayon de 20km.

J'ai une méthode VBA pour calculer la distance: distance(lon,lat,lon1,lat1), ça fonctionne bien.

Afin d'éviter le crash d'excel, j'ai utilisé Power Query pour importer ce CSV.

Cependant je ne sais pas comment venir effectuer le calcul de distance sur chaque ligne afin de la récupérer si c'est dans les 20km.

A vrai dire, je ne sais même pas si je suis capable de récupérer la valeur d'une cellule avec VBA.

J'arrive à trouver le CSV à partir de VBA:

J'ai essayé d'utiliser

ActiveWorkbook.Queries.Item(1).Application.Cells(1, 1).Value

Sans résultat.

Merci d'avance de votre aide.

Bonne journée.

Bonjour

Le_Fab a écrit :

J'ai une méthode VBA pour calculer la distance: distance(lon,lat,lon1,lat1), ça fonctionne bien.

Alors il faut la reproduire dans PowerQuery en formule M

Bonjour Chris,

Merci de votre réponse. J'ai créé la fonction permettant de calculer la distance en formule M.

J'ai réussi à créer une colonne "distance" afin de stocker la distance et venir appliquer un filtre de 15km.

Cependant je ne sais pas comment modifier le point par rapport auquel on mesure la distance sans avoir à ouvrir PowerQuery pour modifier le fonction:

L'objectif étant d'entrer la position GPS et en cliquant sur un bouton de venir mettre à jour la fonction distance.

Je pense qu'il faudrait modifier la formule, cependant je ne sais pas si c'est possible en VBA:

let
    Source = Folder.Files("C:\Users\Moi\Desktop\Tourisme\Datatourisme"),
    #"Fichiers masqués filtrés1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Appeler une fonction personnalisée1" = Table.AddColumn(#"Fichiers masqués filtrés1", "Transformer le fichier à partir de Datatourisme", each     #"Transformer le fichier à partir de Datatourisme"([Content])),
    #"Colonnes renommées1" = Table.RenameColumns(#"Appeler une fonction personnalisée1", {"Name", "Source.Name"}),
    #"Autres colonnes supprimées1" = Table.SelectColumns(#"Colonnes renommées1", {"Source.Name", "Transformer le fichier à partir de Datatourisme"}),
    #"Colonne de tables développée1" = Table.ExpandTableColumn(#"Autres colonnes supprimées1", "Transformer le fichier à partir de Datatourisme", Table.ColumnNames(#"Transformer le fichier à partir de Datatourisme"(#"Exemple de fichier"))),
    #"Type modifié" = Table.TransformColumnTypes(#"Colonne de tables développée1",{{"Source.Name", type text}, {"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}}),
    #"Colonnes renommées" = Table.RenameColumns(#"Type modifié",{{"Column1", "Nom_du_POI"}, {"Column2", "Latitude"}, {"Column3", "Longitude"}, {"Column4", "Adresse_postale"}, {"Column5", "Contacts"}, {"Column6", "Description"}}),
    #"Premières lignes supprimées" = Table.Skip(#"Colonnes renommées",1),
    #"Colonnes supprimées" = Table.RemoveColumns(#"Premières lignes supprimées",{"Source.Name"}),
    #"Texte nettoyé" = Table.TransformColumns(#"Colonnes supprimées",{{"Nom_du_POI", Text.Clean, type text}, {"Latitude", Text.Clean, type text}, {"Longitude", Text.Clean, type text}, {"Adresse_postale", Text.Clean, type text}, {"Contacts", Text.Clean, type text}, {"Description", Text.Clean, type text}}),
    #"Valeur remplacée" = Table.ReplaceValue(#"Texte nettoyé",".",",",Replacer.ReplaceText,{"Latitude", "Longitude"}),
    #"Type modifié1" = Table.TransformColumnTypes(#"Valeur remplacée",{{"Latitude", type number}, {"Longitude", type number}}),
    #"Fonction personnalisée appelée" = Table.AddColumn(#"Type modifié1", "Distance", each Requête1(45.903039, 0.510672, [Latitude], [Longitude])),
    #"Lignes filtrées" = Table.SelectRows(#"Fonction personnalisée appelée", each [Distance] <= 15),
    #"Lignes triées" = Table.Sort(#"Lignes filtrées",{{"Distance", Order.Ascending}})
in
    #"Lignes triées"

En changeant plus spécifiquement cette ligne:

#"Fonction personnalisée appelée" = Table.AddColumn(#"Type modifié1", "Distance", each Requête1(45.903039, 0.510672, [Latitude], [Longitude])),

Pensez vous qu'il soit possible de faire cela en VBA ? Il y a t'il une solution plus simple ?
Merci d'avance.
Fabien.

Bonjour

On peut passer des paramètres à une requête ou appeler la fonction depuis une requête en lui passant les paramètres

2 solutions possibles pour cela :

1. la plus simple nommer les cellules Excel où sont stockées ces variables et récupérer leur valeur dans PowerQuery
   Exemple : Cellule nommée Longitude
   Dans PowerQuery on crée une requête vide nommée Longitude et on met dans la barre de formule =Excel.Workbook(){[Name="Longitude"]}[Content][Column1]{0}
   Il suffit ensuite dans l'appel à la fonction de passer Longitude et Latitude (créée sur le même principe)
   Il faut en général régler les paramètres de confidentialité de PowerQuery
   Fichier, Options et paramètres, Options de requête, partie GLOBAL : Confidentialité, Toujours ignorer les paramètres de niveau de confidentialité
2. Créer un paramètre nommé Longitude dans PowerQuery, lui donner une valeur. Idem pour Latitude

Dans le 1er cas on aura soit une actualisation manuelle, soit par VBA au changement de valeur d'une des cellules.
Dans le second cas VBA modifiera la valeur des paramètres et actualisera également

Dans le code M la lignes dans les 2 cas sera

Table.AddColumn(#"Type modifié1", "Distance", each Requête1(Latitude, Longitude, [Latitude], [Longitude]))

(ou quelque chose d'approchant car n'ayant pas fichier, je ne vois pas concrètement. Par exemple les séparateurs décimaux peuvent souvent être modifiés par un typage approprié plutôt qu'un remplacement...)