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

oguruma · 16 Février 2024

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)

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

Résultat

Autre exemple

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

Comment parvenir à cela ?

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

==> 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 !

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

Les plages nommées

L'onglet de paramétrage Params

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).

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

Organisation sous PowerQuery
Il n'y a que cela :

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

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 ]

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

oguruma

XLDnaute Occasionnel

Pièces jointes

Discussions similaires