Power Query Data Model - renvoyer le résultat d'une requête PQ sans passer par une feuille

Staple1600 · 6 Octobre 2024

Bonsoir le fil

Suite aux divers échanges avec @jurassic pork dans cette discussion, j'ouvre cette discussion

Le but recherché est de ne pas passer par un tableau structuré pour exploiter le résultat d'une requête PQ

Les essais de @jurassic pork l'ont mené vers le Data Model (ou modèle de données)

Pour ma part, je traine les pieds avec ce biais (qui passe "temporairement" par une feuille)

D'abord la requête PQ

PowerQuery:

let
Source = Json.Document(Web.Contents("https://geo.api.gouv.fr/communes?nom=Rennes")),
AA = Table.FromRecords(Source),
BB = Table.ExpandListColumn(AA, "codesPostaux"),
FIN = Table.Distinct(BB, {"nom"})
in
FIN

Ensuite en manipulant(*) PQ tout en laissant tourner l'enregistreur de macros, je suis arrivé à ce code VBA
(en faisant Charger dans et en cochant Ajouter au modèle de données)

Code:

Sub Macro1()
Dim LOB As ListObject, tablo
Application.ScreenUpdating = False
On Error Resume Next
ActiveSheet.ListObjects(1).Delete
    With ActiveSheet.ListObjects.Add(SourceType:=4, Source:=ActiveWorkbook. _
        Connections("Requête - Requête1"), Destination:=Range("$A$1")).TableObject
        .RefreshStyle = 1
        .Refresh
    End With
    Set LOB = ActiveSheet.ListObjects(1)
    tablo = LOB.DataBodyRange.Value
MsgBox "Ville: " & tablo(1, 1) & vbCr & "CP: " & tablo(1, 7) & vbCr & "Population: " & tablo(1, 8)
ActiveSheet.ListObjects(1).Delete
End Sub
Sub Ajout()
Workbooks("Classeur2").Connections.Add2 "Requête - Requête1", _
"Connexion à la requête « Requête1 » dans le classeur.", _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Requête1;Extended Properties=", """Requête1""", 6, True, False
End Sub

J'en appelle, non pas à la tendresse, mais aux grands MamaMouchi(*) (l'ironie en moins) de la Requête Puissante pour éclairer mon chemin.

PS: @jurassic pork , je t'invite dans la partie si tu le souhaites

(*) d'autres les nomment: PQwan Kenobi, ou utilisateurs chevronnés de PQ

jurassic pork · 7 Octobre 2024

Hello Staple1600,
je donne une possibilité ici

Ami calmant, J.P

Staple1600 · 7 Octobre 2024

Bonjour le fil

@jurassic pork
Moi qui avait créé spécialement cette discussion PQ, c'est loupé
(pour ne pas encombrer le fil de Nicolas,
qui nous disait ne pas être à l'aise avec PQ.)

Je fais néanmoins un petit up (comme on disait jadis)

Et je vais voir ta réponse dans l'autre fil.

Staple1600 · 7 Octobre 2024

Re

De mon côte, j'ai allégé le code M de @jurassic pork qui améliorait le code M de Staple1600
Discret hommage à Alain Delon. Oui je parle de moi à la 3ième personne.

PowerQuery:

let
Ville = Excel.CurrentWorkbook(){[Name="MaVille"]}[Content]{0}[Column1],
Source = Json.Document(Web.Contents("https://geo.api.gouv.fr/communes?nom=" & Ville)),
AAA = Table.FromRecords(Source),
BBB= Table.TransformColumns(AAA, {"codesPostaux", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
BBB

Je serais ravi si les experts XLDiens en PowerQuery passaient jeter un oeil dans ce fil
(histoire que @jurassic pork et moi, on se sente moins seul dans celui-ci

Staple1600 · 7 Octobre 2024

Re

@jurassic pork
J'ai modifié ton code de test ainsi

VB:

Sub TestGetPQResult_BIS()
Choix_Commune = InputBox("Nom commune?", "Rennes")
[MaVille] = Choix_Commune
ActiveWorkbook.RefreshAll
GetPQResult "codeINSEE"
End Sub

jurassic pork à dit:
Attention il y a une option de sécurité à modifier (si un gourou de PQ peut me dire comment éviter d'avoir ce problème)

De quelle option parles-tu?
Je n'ai pas eu de problème en testant ton code.

jurassic pork · 7 Octobre 2024

Staple1600 à dit:
De quelle option parles-tu?
Je n'ai pas eu de problème en testant ton code.

heu je me rappelle plus mais si tu n'as pas de message. Il me semble que c'était dû à l'utilisation d'une cellule nommée dans le langage M.
Au fait ma procédure Add_ConnectionDM fonctionne correctement ?

Staple1600 · 7 Octobre 2024

Re

@jurassic pork

Au fait ma procédure Add_ConnectionDM fonctionne correctement ?

Oui
Comme le prouve le message#5 , non ?

Staple1600 · Mardi à 21:08

Bonjour le fil

Un petit up

Pas de powerqueristes à l'horizon ?

Staple1600 · Mercredi à 22:48

Bonjour

En attendant que les powerquistes du forum m'apportent leurs lumières et/ou du code M ciselé.

Voici mes derniers essais du béotien en PQ que je suis .

@jurassic pork
Puisqu'on utilise une feuille pour saisir le nom de la ville, autant continuer, non ?
Un userform avec un CommandButton et un ListBox

VB:

Private a As Variant
Private Sub UserForm_Initialize()
 [MaVille] = InputBox("Nom de la commune ?", "Choix", "Rennes")
ActiveWorkbook.Connections("Requête - pq_INSEE").Refresh
End Sub
Private Sub CommandButton1_Click()
a = Feuil3.ListObjects(1).DataBodyRange.Value
a = Application.Index(a, Evaluate("row(1:" & UBound(a) & ")"), Array(1, 3, 5))
ListBox1.List = a
End Sub

Cette macro associée à un "bouton" sur une feuille

VB:

Sub Show_USF()
UserForm1.Show 0
End Sub

EDITION
Ca fonctionne même si la feuille recevant la requête PQ et la feuille avec le nom MaVille, sont masquées.
Sur mon PC, le temps de réponse n'est pas si long.

@A tous
=>lecteurs de passage (PQueristes ou pas)

Pour ma gouverne et par curiosité, je cherche toujours comment passer un paramètre pas le biais de VBA à une requête PQ.
(ici en l'occurrence le nom de la ville)

jurassic pork · Jeudi à 07:33

Staple1600 à dit:
Pour ma gouverne et par curiosité, je cherche toujours comment passer un paramètre pas le biais de VBA à une requête PQ.
(ici en l'occurrence le nom de la ville)

Hello,
voici une méthode pour passer un paramètre à une requête PowerQuery en VBA
A - Il faut créer une requête de paramètre
Vous pouvez utiliser un paramètre pour modifier automatiquement une valeur dans une requête et éviter de modifier la requête à chaque fois pour modifier la valeur. Il vous suffit de modifier la valeur du paramètre. Une fois que vous avez créé un paramètre, il est enregistré dans une requête de paramètre spéciale que vous pouvez facilement modifier directement à partir d’Excel.

Sélectionnez Données > Obtenir des données > d’autres sources > lancer Éditeur Power Query.
Dans l'Éditeur Power Query, sélectionnez Accueil > Gérer les paramètres > Nouveaux paramètres.
Dans la boîte de dialogue Gérer le paramètre, sélectionnez Nouveau.

Définissez les éléments en fonction des besoins exemple :

Dans votre requête vous pouvez alors utiliser votre paramètre comme une variable exemple :

PowerQuery:

let
Source = Json.Document(Web.Contents("https://geo.api.gouv.fr/communes?nom=" & MaVille)),
AAA = Table.FromRecords(Source),
BBB= Table.TransformColumns(AAA, {"codesPostaux", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
BBB

Voici un exemple de code VBA pour passer un paramètre à la requête PowerQuery :

VB:

Sub TestGetPQResult()
  Dim bm As New cBenchmark
  Choix_commune = "Ger"
 'Choix_Commune = InputBox("Nom commune?", "Rennes")
  ChangeParameterValue "MaVille", Choix_commune
  ActiveWorkbook.RefreshAll
  GetPQResult "RecupCodeINSEE"
End Sub

Sub ChangeParameterValue(ParameterName As String, ParameterValue)
    Dim qry As WorkbookQuery
    Dim formula As Variant
    Set qry = ActiveWorkbook.Queries(ParameterName)
    formula = Split(qry.formula, " meta [")
    If VarType(ParameterValue) = vbString Then
               formula(0) = """" & ParameterValue & """"
    Else
               formula(0) = ParameterValue
    End If
    qry.formula = Join(formula, " meta [")
End Sub

La procédure ChangeParameterValue c'est moi qui l'ai "bricolée" car celle que j'avais trouvée ne gérait que les paramètres de type chaîne. J'ai testé avec des paramètres de type entier et chaîne, cela a l'air de fonctionner.
A vérifier qu'elle fonctionne pour tous les types et si il y a un problème l'indiquer.

Ami calmant, J.P

merinos · Jeudi à 08:23

Bonjour @Staple1600 , @jurassic pork ,
Et a Tous , bien entendu,

Il y a toujours une methode pour lire des champs nommés...

let
FilePath= Excel.CurrentWorkbook(){[Name="File_Path"]}[Content]{0}[Column1],
FileName= Excel.CurrentWorkbook(){[Name="File_Name"]}[Content]{0}[Column1],

Source = Folder.Files(FilePath),
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], FileName)),
#"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"Date modified", Order.Descending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Added Index", "ce jour", each DateTime.Date(DateTime.LocalNow())<=Date.AddDays(DateTime.Date([Date created]) , 1)),
#"Filtered Rows2" = Table.SelectRows(#"Added Custom1", each [ce jour] = true),
#"Added Custom" = Table.AddColumn(#"Filtered Rows2", "Custom", each [Folder Path] & [Name]),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Index", "Custom"})
in
#"Removed Other Columns"

C'est de loin la méthode la plus simple que j'ai trouvé...
On déclare des variables ... et on les emploies.

FilePath= Excel.CurrentWorkbook(){[Name="File_Path"]}[Content]{0}[Column1],

on peut lire ceci comme :

FilePath est la zone nommée "File_Path" , on prends son contenu dont on garde seulement la premiere ligne et la premiere colonne.

PS: j'ai pas mis en code afin de pouvoir mettre des couleurs

Merinos

Staple1600 · Jeudi à 19:58

Bonsoir @jurassic pork , @merinos

@jurassic pork
1er test simple avec un paramètre dans PQ comme tu l'as expliqué
J'ai commenté la 1er ligne sinon erreur

Code:

Sub TestGetPQResult()
 ' Dim bm As New cBenchmark
Choix_Commune = InputBox("Nom commune?", "Rennes")
ChangeParameterValue "MaVille", Choix_Commune
ActiveWorkbook.RefreshAll
End Sub

Il faut que je recommence le test pour utiliser ton code
(car il y a des boutsde codes M publiés dans la discussion de Nicolas
Jai testé sans faire gaffe et il me manquait des procédures qui sont là-bas

@merinos
Le but du jeu ici (simplement par curiosité)
C'était de volontairement ne passer par un ListOject pour récupérer les données issues de PQ
Ce que fait la solution postée par @jurassic pork

J'essaie de voir toujours, par curiosité et pour ma gouverne, si il y a d'autres syntaxe M pour ce faire.

D'où mon appel aux Powerqueristes chevronnés du forum et leur petits frères/soeurs que j'appelle les codeurs_M à jeter un œil dans ce fil.

Power Query Data Model - renvoyer le résultat d'une requête PQ sans passer par une feuille

Staple1600

XLDnaute Barbatruc

jurassic pork

XLDnaute Occasionnel

Staple1600

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

jurassic pork

XLDnaute Occasionnel

Staple1600

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

Staple1600

XLDnaute Barbatruc

jurassic pork

XLDnaute Occasionnel

merinos

XLDnaute Accro

Staple1600

XLDnaute Barbatruc

Discussions similaires