Power Query Paramètre variable requête ODBC

combory · 15 Mai 2023

Bonjour,

Je récupère mes données via une requête ODBC dans POWER QUERY
Dans le fichier joint (Requête1 du POWER QUERY), j'ai un "WHERE" (DAPP) qui filtre les enregistrements et qui du coup limite le nombre d'enregistrement.

Est-il possible de rendre variable le "WHERE" avec une valeur que je sélectionnerai dans l'onglet "Paramètre" ?

J'ai créé une Liste (Requête2 du POWER QUERY) de paramètres possible, mais je ne sais pas comment l'exploiter.

NB : j'ai ajouté un WHERE sur la donnée AV pour alléger le fichier

Hasco · 16 Mai 2023

Bonjour,

Dans le fichier joint vous trouverez dans la feuille paramètres, un jumeau ou presque du tableau "Requête2" qui est alimenté par la formule suivante :

=SI(AGREGAT(3;3;Paramètre!$A12)>0;INDEX(Requête2[Requête2];LIGNE(Requête2[@Requête2])-1);0)

Agregat nous permet d'utiliser certaines formules statistiques sur un tableau en ne prenant pas en compte les lignes masquées et/ou autre caractéristiques.
Ici par le premier paramètre (3) je lui demande de calculer le nombre de valeur de la ligne correspondante dans Requête2. Si la ligne est cachée, le résultat sera 0 sinon on renvoie par index le contenu de la cellule de "Requête"
Donc dans notre nouveau tableau nommé ici "Prm_Items" nous aurons des zéros ou les valeurs non...

Hasco · 15 Mai 2023

Bonsoir,

Je ne sais pas si j'ai tout compris.

La chaîne de connexion est une chaîne comme une autre, elle peut être construite à partir de variable(s) concaténée(s).

Je suppose que c'est la liste de numéros de Requête2 que vous voulez passer à la clause where.
Dans votre approche, vous pouvez :
Tansformer le tableau en chaine de caratères "20060101,20070101,20070201,20070301,20080201,20110301,20130401,20140301,20160101,20180101,20190101,20200101,20220101,20230101" pour l'intégrer entre parenthèse dans la clause where in

Notez le "IN (" & Liste & ")" après DAPP

PowerQuery:

let
    Liste = Text.Combine(List.Transform(Excel.CurrentWorkbook(){[Name="Requête2"]}[Content][Requête2], Text.From),","),
    Source = Odbc.Query("dsn=lfb2", "SELECT #(lf)#(tab)T1.AV,#(lf)#(tab)T1.NODEP,#(lf)#(tab)T1.TRSDEP,#(lf)#(tab)T1.ZONE,#(lf)#(tab)T1.FORFAI,#(lf)#(tab)T1.TRCDEV,#(lf)#(tab)T1.DAPP,#(lf)#(tab)T3.NLIEUE#(lf)FROM #(lf)#(tab)ROXDTA400.TRTF0010 T1#(lf)#(tab)LEFT OUTER JOIN ROXDONNEE.LIEUENL T3#(lf)#(tab)ON #(lf)#(tab)T1.NODEP = T3.CODGEO#(lf)WHERE #(lf)DAPP IN (" & Liste & ")#(lf)AND AV = 'A'#(lf)#(tab)" ),
    #"Colonnes renommées" = Table.RenameColumns(Source,{{"AV", "Achat / Vente"}, {"TRSDEP", "Site 3"}, {"ZONE", "Zone"}, {"FORFAI", "Forfait"}, {"TRCDEV", "Devise"}, {"DAPP", "Date d'application"}, {"NLIEUE", "Code Lieu"},   {"NODEP", "CPT"}})
in
    #"Colonnes renommées"

Supprimez tout les #(lf) et #(tab) à l'intérieur de la chaîne de caractère. C'est l'opération de collage que vous avez faite qui les a insérés mais ils sont inutiles ici.

Vous vous doutez bien que je n'ai pas pu tester.

Dans une autre approche vous pourriez sans doute fusionner votre requête "Requête2" sur la clef du même nom et votre autre requête sur le champ DAPP.
Mais je crois que cette méthode serait plus lente que la première, PQ étant obligé de rapatrier la table distante pour en comparer les clefs. Alors que dans l'autre approche c'est le serveur qui se charge du boulot.

Si ça ne va pas, essayez d'être plus précis dans la formulation de vôtre demande.

combory · 16 Mai 2023

Bonjour Hasco et merci pour cette réponse,

J'ai mis à jour mon fichier avec vos modifications.
J'ai également supprimé les #(lf) et #(tab).
En fait j'écris mon SQL sur NOTEPAD++, avec des retours à la ligne et des tabulations pour avoir quelque chose de lisible, puis je fais copier/coller dans l'instruction SQL de l'importation à partir d'ODBC. Je n'avais jamais essayé de les supprimer... Merci pour ça.

Bien sûr votre code fonctionne, mais pas le filtre. Toutes les données remontent.

Les données de l'onglet Paramètre sont aussi issues de la base.
L'idée serait de filtrer sur une ou plusieurs données du tableau Requête2 sur l'onglet Paramètre, et que lors de l'actualisation de l'onglet Données, seules ces données soient retenues.
Par exemple, si je filtre mon tableau Requête2 pour ne garder que 20200101 et 20230101 (il s'agit de dates), seules les données concernées sont retenues lors de l'actualisation.

Pour pouvoir joindre le fichier, j'ai volontairement réduit le nombre de lignes, mais en réalité, sans filtres, le nombre de données dépassent le millions de lignes.
Donc si je veux comparer une période avec une autre, j'ai besoin de ce filtre.

Je ne peux pas joindre le fichier modifié, car comme l'ensemble des données remontent, il pèse plus de 4 Mo.
J'espère avoir été plus précis, et merci pour votre aide.

Hasco · 16 Mai 2023

Bonjour,

Dans le fichier joint vous trouverez dans la feuille paramètres, un jumeau ou presque du tableau "Requête2" qui est alimenté par la formule suivante :

=SI(AGREGAT(3;3;Paramètre!$A12)>0;INDEX(Requête2[Requête2];LIGNE(Requête2[@Requête2])-1);0)

Agregat nous permet d'utiliser certaines formules statistiques sur un tableau en ne prenant pas en compte les lignes masquées et/ou autre caractéristiques.
Ici par le premier paramètre (3) je lui demande de calculer le nombre de valeur de la ligne correspondante dans Requête2. Si la ligne est cachée, le résultat sera 0 sinon on renvoie par index le contenu de la cellule de "Requête"
Donc dans notre nouveau tableau nommé ici "Prm_Items" nous aurons des zéros ou les valeurs non filtrées de Requêtes2. Je vous ai fait une copie de ce tableau dans feuil1 pour que vous puissiez visionner les choses, les lignes de paramètres étant masquées par les filtres.

C'est "Prm_Items" que pq doit interroger pour construire la liste des items pour la clause WHERE IN

=Text.Combine(List.Transform(List.Select(Excel.CurrentWorkbook(){[Name="Prm_Items"]}[Content][Item],each _ > 0),Text.From),",")

En C1 de paramètres la formule suivante vous donne le nombre de lignes non masquées de "Requête2" (pour info)

=AGREGAT(3;3;Requête2[Requête2])

Et en G1 vous avez le nombre de valeurs supérieures à zéro.

combory · 16 Mai 2023

Bonjour Hasco,

C'est tout simplement génial.
Je te remercie pour le temps consacré et la précision de tes explications qui me permet de mieux comprendre le cheminement.

Power Query Paramètre variable requête ODBC

combory

XLDnaute Junior

Pièces jointes

Hasco

Hasco

XLDnaute Barbatruc

combory

XLDnaute Junior

Hasco

XLDnaute Barbatruc

Pièces jointes

combory

XLDnaute Junior

Discussions similaires