POWERQUERY :: Lancement de requêtes à tiroir - dynamiques - paramétrables - code des requêtes sur disque - Exemple de mise en oeuvre

oguruma

XLDnaute Occasionnel
Hello,
Un post qui pourrait peut-être orienter le développement de vos applications Excel faisant appel à des requêtes de sélections multiples avec un seul et unique affichage (et non comme on pourrait le faire, un onglet par requête)

Exemple : Base de données RH - totalement fictive
Installation : Dézipper le fichier dans un vos dossier et adapter le paramétrage - ceci sera expliqué à la fin de ce post

La sélection de la requête à exécuter peut se faire sous cette forme (libre à vous pour l'IHM)

1708036644792.png


==> Sélection de la requête via la liste déroulante
==> Activer
1708036720193.png


Résultat

1708036749511.png


Autre exemple
1708036787323.png

1708036810604.png


Autre exemple avec critères H/F et type de contrat
1708036859522.png

1708036883329.png


Comment parvenir à cela ?

Organisation du répertoire de l'application (exemple - à personnaliser selon vos besoins)
1708036968172.png


==> on y trouve que le fichier Excel de l'application, le dossier contenant les données à exploiter DBRH le dossier contenant toutes les requêtes PQ_DLL et le dossier de paramétrage Params - ET c'es tout !

1708037105946.png


1708037124242.png


1708037138765.png


Le paramétrage
Pour cela je fais appel aux petits utilitaires déposés il y a quelques semaines sur la lecture de fichiers paramètres stockés sur disque - j'ai retenu le format suivant parmi ceux qui avaient été présentés

Deux types :
- les paramètres généraux
{
// Dossier contenant les requêtes
[ PARAMETRE = "DOSSIER_DLL", VALEUR = "D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqQUERY_MULTIPLE\PQ_DLL" ] ,
// Extension des requêtes par défaut
[ PARAMETRE = "EXTENSION_DLL", VALEUR = ".pq" ],
// Emplacement des données de l'application
[ PARAMETRE = "TBRH", VALEUR="D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqQUERY_MULTIPLE\DBRH\DBRH.xlsx"]

},

type table[PARAMETRE = Text.Type, VALEUR = Any.Type ]

- Les paramètres décrivant les requêtes à appeler


//------------------------------------------------------------------------------------------------
// Fichier paramètres pour les requêtes qui seront appelées par la requête pivot de lancement
//------------------------------------------------------------------------------------------------

{
[ PARAMETRE = "RQ01", REQUETE = "RQ_TBRH_ALL", LIBELLE = "Toute la table par matricule"] ,
[ PARAMETRE = "RQ02", REQUETE = "RQ_INFORMATIQUE_CDI_NORD", LIBELLE = "Informatique nord en CDI" ] ,
[ PARAMETRE = "RQ03", REQUETE = "RQ_MASSE_SALARIALE", LIBELLE = "Masse salariale globale" ] ,
[ PARAMETRE = "RQ04", REQUETE = "RQ_MASSE_SALARIALE_CONTRAT", LIBELLE = "Masse salariale par contrat" ] ,
[ PARAMETRE = "RQ06", REQUETE = "RQ_EFFECTIFS_HF", LIBELLE = "Effectifs HF" ],
[ PARAMETRE = "RQ06", REQUETE = "RQ_EFFECTIFS_HF_DEPART", LIBELLE = "Effectifs HF par departement" ],
[ PARAMETRE = "RQ06", REQUETE = "RQ_SEXE_TYPE_CONTRAT", LIBELLE = "Table par type et contrat" ]

},

type table[PARAMETRE = Text.Type, LIBELLE = Any.Type, REQUETE = Text.Type ]

Organisation du fichier Excel

Les requêtes sont sélectionnées via cette liste
1708037448811.png


Les plages nommées

1708037564182.png


L'onglet de paramétrage Params

1708037596174.png



Afin de localiser le répertoire qui héberge l'application : =GAUCHE(CELLULE("nomfichier";$A$1);TROUVE("[";CELLULE("nomfichier";$A$1))-2) et c'est à partir de celui-ci que l'on va détecter la présence des requêtes et du fichier de données à exploiter

Pour ces éléments ils sont récupérés automatiquement par formule. Les requêtes exploitent directement ces noms de champs pour leur exécutions (ex. passage de paramètres).
1708037804156.png


1708037826882.png


1708037844354.png


1708037862742.png


Contenu du fichier paramètres des requêtes à gérer
1708037893896.png


Organisation sous PowerQuery
Il n'y a que cela :
1708037939762.png

Paramètres généraux
PowerQuery:
let
    SourceParams = fnTbGetParamFromFileRecords()
in
    SourceParams


Paramètres des requêtes
PowerQuery:
let
    SourceParams = fnTbGetParamFromFileRecords("ParamsQueryMultiple")
in
    SourceParams

Source pour lire les fichiers paramètres un peu adapté par rapport à la version initiale déposée il y a quelques temps

PowerQuery:
/* Exemple
{
  [ PARAMETRE = "PARAM1", VALEUR = "STRING1"     ] ,
  [ PARAMETRE = "PARAM2", VALEUR = "STRING2"     ] ,
  [ PARAMETRE = "PARAM4", VALEUR = 25            ] ,
  [ PARAMETRE = "PARAM5", VALEUR = "16/10/2023"  ] ,
  [ PARAMETRE = "PARAM6", VALEUR = null] 
},

type table[PARAMETRE = Text.Type, VALEUR = Any.Type ]

*/

let
   fnTbGetParamFromFileRecords = (
                              optional ppFileParam            as text,
                              optional ppExtension   as text,
                              optional ppFolder      as text,
                              optional ppDelimiter   as text,
                              optional ppColParam    as text,
                              optional ppColVal      as text
         ) =>
      let
         SUBFOLDER_PARAMS="Params",
         CurrentDirectory = Excel.CurrentWorkbook(){[Name="DOSSIER_COURANT"]}[Content]{0}[Column1],       
         PARAM_PATH_DEFAULT=CurrentDirectory & "\" & SUBFOLDER_PARAMS,       
         PARAM_EXTENSION_DEFAULT = ".ini",
         PARAM_DELIMITER_DEFAULT = "=", 
         DEFAULT = PARAM_PATH_DEFAULT & "\",
         BACK_SLASH="\",
         COL_PARAMETRE="PARAMETRE",
         COL_VALEUR="VALEUR",

         pParam = if ppFileParam <> null then
                     ppFileParam
                     else "Parametres",
         Extension =  if ppExtension is null then
                         PARAM_EXTENSION_DEFAULT
                         else ppExtension,                     
         PathInitial = if ppFolder <> null then
                          ppFolder
                          else DEFAULT,
         Delimiter = if ppDelimiter <> null then
                        ppDelimiter
                        else PARAM_DELIMITER_DEFAULT,
         Parametre = if ppColParam <> null then
                        ppColParam else
                        COL_PARAMETRE,
         Valeur = if ppColVal <> null then
                        ppColVal else
                        COL_VALEUR,
      
         PathProcedure = PathInitial & (if Text.End(PathInitial, 1) <> BACK_SLASH then BACK_SLASH else ""),
         FileParam = PathProcedure & pParam & Extension, 
      
         Source1 = Text.FromBinary(Binary.Buffer(File.Contents(FileParam))),
         Source2 =  "Table.FromRecords ( " & Source1 & " )",
         ToTable = Expression.Evaluate(Source2, #shared)             
          
      in
         ToTable
in
   fnTbGetParamFromFileRecords

fnGetParameter a évolué :

PowerQuery:
let fnGetParameter =
    (
        pTable          as any,
        pName           as text,
        optional pParam as text,
        optional pVal   as text
    ) =>

    let
        ParamSource = if pTable is text then
                         Excel.CurrentWorkbook(){[Name=pTable]}[Content]
                         else pTable,

        Parametre   = if pParam <> null then
                         pParam
                         else "PARAMETRE",

        Valeur      = if pVal <> null then
                         pVal
                         else "VALEUR",

        StrParam="each ([" & Parametre & "]) = " & """" & pName & """",
        EvalParam=Expression.Evaluate(StrParam,[ParamSource=ParamSource]),     
      
        ParamRow = Table.SelectRows(ParamSource, EvalParam),
      
        Value= if Table.IsEmpty(ParamRow)=true
                  then null
                  else Record.Field(ParamRow{0},Valeur)
    in
        Value
in
    fnGetParameter


Et la requête ci-dessous c'est le nerf "de la guerre" qui tient en très peu de lignes
PowerQuery:
let

    SelectedQUery = Excel.CurrentWorkbook(){[Name="SELECT_REQUETE"]}[Content]{0}[Column1],
    LoadTBRH = fnLoadPQAny(SelectedQUery)
  
in
    LoadTBRH


Requête de chargement des données
PowerQuery:
// RQ_LOAD_TBRH.pq

let
    Function = (
        PathTBRH as text,
        optional pSex as text,
        optional pContrat as text
        ) =>
        let
            Source = Excel.Workbook(File.Contents(PathTBRH), null, true),
            TBRH_Sheet = Source{[Item="TBRH",Kind="Sheet"]}[Data],
            Promote = Table.PromoteHeaders(TBRH_Sheet, [PromoteAllScalars=true]),
            TbSex= if pSex is null then
                      Promote
                      else Table.SelectRows(Promote, each ([SEXE] = pSex)),
            TbContrat= if pContrat is null then
                      TbSex
                      else Table.SelectRows(TbSex, each ([CONTRAT] = pContrat)),
          
            TypeModif = Table.TransformColumnTypes(TbContrat,{{"MLE", Int64.Type}, {"NOM", type text}, {"PRENOM", type text}, {"SEXE", type text}, {"DEPT", Int64.Type}, {"SECTEUR", type text}, {"AGE", Int64.Type}, {"CONTRAT", type text}, {"DATE ENTREE", type date}, {"SERVICE", type text}, {"SALAIRE", type number}})
        in
            TypeModif
in
    Function

Requête de référence
PowerQuery:
// TB_REFERENCE_DBRH

let

    PathTBRH=fnGetParameter(TB_PARAMS_GENRAUX, "TBRH", null, null),
    FunctionLoadTBRH = fnLoadPQAny("RQ_LOAD_TBRH"),
    ToTableRH=FunctionLoadTBRH(PathTBRH)
in
    ToTableRH

PowerQuery:
// RQ_TBRH_ALL.pq

let
/*
    pCodeName            as text,  // Nom du module à charger
    optional pExtension  as text,  // Extension du module (.pq par défaut)
    optional pFolder     as text   // Dossier où est stocké le module
*/
    //LoadTBRH = LOAD_TBRH
    Source=fnLoadPQAny("TB_REFERENCE_DBRH"),
    LoadTBRH=Source
in
    LoadTBRH


.... le plus simple étant de télécharger le fichier et d'installer l'application pour bien comprendre le montage qui peut paraître compliqué pour les non initiés mais très pratique quand on en a la maîtrise. Ainsi vous livrez un fichier Excel léger et tout se passe à l'extérieur des données. Ainsi on sépare données - code

Installation
1- dézipper le fichier dans un dossier selon mon exemple : D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqQUERY_MULTIPLE
2- vous aurez ce style d'arborescence
1708038937348.png

3- Dossier Params adapter le fichier Parametres.ini
puis adapter les paramètres DOSSIER_DLL et TBRH à votre environnement selon l'emplacement où vous avez dézippé le fichier
{
// Dossier contenant les requêtes
[ PARAMETRE = "DOSSIER_DLL", VALEUR = "D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqQUERY_MULTIPLE\PQ_DLL" ] ,
// Extension des requêtes par défaut
[ PARAMETRE = "EXTENSION_DLL", VALEUR = ".pq" ],
// Emplacement des données de l'application
[ PARAMETRE = "TBRH", VALEUR="D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__pqQUERY_MULTIPLE\DBRH\DBRH.xlsx"]

},

type table[PARAMETRE = Text.Type, VALEUR = Any.Type ]

et logiquement vous n'avez pas à modifier celui ci-dessous sauf si souhaitez ajouter d'autres requêtes il faudra les renseigner dans ce dernier en respectant le format de paramétrage en mode [record] à la sauce powerquery. Le champ PARAMETRE n'est pas exploité. Prévu pour des besoins d'évolutions ;) - LIBELLE est modifiable à votre aise. REQUETE aussi modifiable mais dans certains selon vos besoin en tenir compte dans les requêtes elles-mêmes.

//------------------------------------------------------------------------------------------------
// Fichier paramètres pour les requêtes qui seront appelées par la requête pivot de lancement
//------------------------------------------------------------------------------------------------

{
[ PARAMETRE = "RQ01", REQUETE = "RQ_TBRH_ALL", LIBELLE = "Toute la table par matricule"] ,
[ PARAMETRE = "RQ02", REQUETE = "RQ_INFORMATIQUE_CDI_NORD", LIBELLE = "Informatique nord en CDI" ] ,
[ PARAMETRE = "RQ03", REQUETE = "RQ_MASSE_SALARIALE", LIBELLE = "Masse salariale globale" ] ,
[ PARAMETRE = "RQ04", REQUETE = "RQ_MASSE_SALARIALE_CONTRAT", LIBELLE = "Masse salariale par contrat" ] ,
[ PARAMETRE = "RQ05", REQUETE = "RQ_EFFECTIFS_HF", LIBELLE = "Effectifs HF" ],
[ PARAMETRE = "RQ06", REQUETE = "RQ_EFFECTIFS_HF_DEPART", LIBELLE = "Effectifs HF par departement" ],
[ PARAMETRE = "RQ07", REQUETE = "RQ_SEXE_TYPE_CONTRAT", LIBELLE = "Table par type et contrat" ]

},

type table[PARAMETRE = Text.Type, LIBELLE = Any.Type, REQUETE = Text.Type ]
 

Pièces jointes

  • 1708037416101.png
    1708037416101.png
    17.3 KB · Affichages: 3
  • 1708037484241.png
    1708037484241.png
    6.5 KB · Affichages: 3
  • $__pqQUERY_MULTIPLE.zip
    200.6 KB · Affichages: 5
Dernière édition:

Discussions similaires

Membres actuellement en ligne

Aucun membre en ligne actuellement.

Statistiques des forums

Discussions
313 259
Messages
2 096 618
Membres
106 688
dernier inscrit
Cherif99