POWERQUERY :: Charger des modules/fonctions à partir du disque et les exécuter à l'issue - Function.Invoke

[oguruma]

Bonjour,

Dans un précédent Post j’ai annoncé la production de requêtes sous forme de modules que l’on peut charger à la volée à partir du disque puis de les exécuter à l’issue.

Voici une possibilité.

En effet une liste de fonctions ou requêtes peuvent être enregistrées dans dossier sans pour autant être chargées dans l’environnement PowerQuery. Elles feraient office de DLL (Dynamic Link Library au sens Windows).

Exemple de dossier

Ici on retrouve en fait les différentes fonctions qui ont été développées pour les opérations de combinaisons de dossiers .csv ou .xlsx.
Ici : https://excel-downloads.com/threads...chiers-csv-ou-des-fichiers-excel-v2.20081238/
Dans ce lien vous trouverez les fichiers de données à installer pour rejouer les différentes requêtes.

Une partie de ces fonctions seront reprises dans le fichier joint.

Plusieurs modes de fonctionnements :

• Soit on charge puis on exécute
• Soit on charge et dans la foulée (via la même fonction on exécute).

On peut charger soit :

• Une table
• Une fonction
• N’importe quel type d’objet

Ce mode de fonctionnement comporte des avantages et des inconvénients.
Avantages :
- on peut coder ses requêtes directement sous un autre éditeur. Personnellement j'utilise VSC avec les extensions PowerQuery. Cela fonctionne très bien et bien mieux que l'éditeur light fourni dans Excel
- les requêtes n'encombrement pas le Workspace PowerQuery
- On peut partager la même requête entre plusieurs classeur Excel = mise à jour unique du code et non devoir le répéter dans chaque classeur
- On peut donc avoir d'un côté le code et de l'autre le résultat des requêtes ; il faudra juste avoir une requête chapeau qui va charger les fonctions (les modules) et les exécuter via la requête de chargement et ou d'exécution. On verra que l'on peut appeler la fonction chargée par Function.Invoke avec donc l'avantage via celle-ci de lancer des exécutions avec des noms de requêtes dynamiques - ça c'est un autre sujet qui n'est pas dans l'objet de ce post... mais peut-être dans un autre
Inconvénients :
- besoin de lancer la fonction (icône) actualiser quand le code source est modifié et ne pas oublier de faire le "save" préalable dans l'éditeur externe
- en cas de livraison ne pas oublier de joindre les requêtes externes avec un mode paramétrage comme celui présenté dans le fichier exemple joint


Chargement d'un module (DLL) - fnLoadPQAny

(
    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
) as any =>

let
    //---------------------------------------------------------------------------
    // Construction du nom du module à charger
    // Il est chargé et renvoyé en tant que fonction pour être exécuté
    //---------------------------------------------------------------------------
    ParamPathDefaut=fnGetParameter("TB_PARAMS", "DOSSIER_DLL"),
    ParamExtDefaut=fnGetParameter("TB_PARAMS", "EXTENSION_DLL"),
    DEFAULT = ParamPathDefaut & "\",
    BACK_SLASH="\",
    Extension =  if pExtension is null then
                    if ParamExtDefaut is null then
                       ".pq" else
                       ParamExtDefaut
                    else pExtension,
                 
    PathInitial = if (pFolder <> null) then pFolder else DEFAULT,
    PathProcedure = PathInitial & (if Text.End(PathInitial, 1) <> BACK_SLASH then BACK_SLASH else ""),
    FileProcedure = PathProcedure & pCodeName & Extension,
 
    //---------------------------------------------------------------------------
    // Chargement du module en mémoire dans le contexte PowerQuery
    //---------------------------------------------------------------------------
    ReturnFunction =
            try
                //-----------------------------------------------------------------------------------             
                // Si la fonction existe on la reprend tel quel
                // Astuce on lance un code bidon afin de voir si ça plante ou pas
                // Si ça ne plante pas alors le module est bien chargé en mémoire par #shared
                // sinon on va le charger à partir du disque
                //----------------------------------------------------------------------------------
                Expression.Evaluate(Text.Replace(pCodeName, ".", "_"), #shared)
                otherwise
                   // try
                        //---------------------------------------------------------------------
                        // Si la fonction n'est pas dans le contexte PowerQuery on la charge
                        // #shared permet de l'ajouter au contexte d'exécution de powerquery
                        //--------------------------------------------------------------------
                        Expression.Evaluate(Text.FromBinary(Binary.Buffer(File.Contents(FileProcedure))), #shared)
                        // Dans la négative on ne lance aucun traitement
                //    otherwise
                //        "Err"

in
 // Debug 
  //FileProcedure

    ReturnFunction

    //Expression.Evaluate(Text.Replace(pCodeName, ".", "_"), #shared)
     //Expression.Evaluate(Text.FromBinary(Binary.Buffer(File.Contents(FileProcedure))), #shared)

Le chargement passe par une table de paramétrage ("comme Dab") dans laquelle on va préciser le dossier où se trouvent les modules/fonctions, l'extension par défaut (.pq par exemple).

Ne pas oublier de paramétrer DOSSIER_DLL. C'est le dossier où vous aurez déposé vos modules (DLL).
Les utilitaires "load" se chargent de lire ce paramètre pour retrouver ces modules.
ParamPathDefaut=fnGetParameter("TB_PARAMS", "DOSSIER_DLL"),

et idem pour
ParamExtDefaut=fnGetParameter("TB_PARAMS", "EXTENSION_DLL"),

Le code de chargement ci-dessus renvoie n'importe quel type d'objet (as any). Pour une table on codera le type as table et pour une fonction on codera le type as function.
Voir les différents codes sources dans le fichier Excel de démonstration joint.

En ce qui concerne ceci
// try
//---------------------------------------------------------------------
// Si la fonction n'est pas dans le contexte PowerQuery on la charge
// #shared permet de l'ajouter au contexte d'exécution de powerquery
//--------------------------------------------------------------------
Expression.Evaluate(Text.FromBinary(Binary.Buffer(File.Contents(FileProcedure))), #shared)
// Dans la négative on ne lance aucun traitement
// otherwise
// "Err"

==> Libre à vous comment vous souhaitez gérer les erreurs au moment du chargement du module DLL

Environnement PowerQuery

Exemples d'utilisation - RQ_Load_myTest_1

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
*/
    xLoaded = fnLoadPQFunction("myTest1"),
    Return = Function.Invoke(xLoaded,{})
in
    Return

On remarque donc que ça se passe en deux temps :
- on charge
- exécute via Function.Invoke

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
*/

// Si l'extenstion n'est pas .pq
//--------------------------------
    xLoaded = fnLoadPQFunction("myTest3",".M"),
    Return = Function.Invoke(xLoaded,{"TestList.xlsx","TBRH"})
in
    Return
[U]

[/U]

Dans celle ci-dessus exemple où passe l'extension .M autre que celle par défaut .pq et on l'exécute avec le passage d'un paramètre "TBRH" pour charger une table.

Variante ci-dessous : le résultat directement dans le in de la requête

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
*/

    xLoaded = fnLoadPQAny("myTest3",".M")
in
    xLoaded("TestList.xlsx","TBRH")

Chargement et exécution - fnLoadAndRunPQFunction

La fonction dédiée est celle-ci : elle se charge du "load" puis à l'issue la requête est exécutée

(
            pFunction            as text,
            optional pListParam  as any,
            optional pExtension  as text,
            optional pFolder     as text,
            optional pSep        as text
) as any =>

        let

            //--------------------------------------------------------
            // Analyse du séparateur pour identifier les paramètres
            //--------------------------------------------------------
            Separator = if pSep is null then
                           ";"
                           else pSep,

            // Une 1ère forme d'appel
            //x= Load("myTest")()

            //--------------------------------------------------------
            // Chargement de la fonction
            //--------------------------------------------------------

            // pqCode= fnLoadPQFunction(pFunction, pExtension, pFolder),

           // on préfère utiliser le retour de type any
           //----------------------------------------------

            pqCode= fnLoadPQAny(pFunction, pExtension, pFolder),[/B]

            //--------------------------------------------------------
            // Préparation des paramètres
            //--------------------------------------------------------
            Param=if pListParam is null then
                     {}
                     else if pListParam is list then
                             pListParam
                             else Text.Split(pListParam, Separator),

            Return = Function.Invoke(pqCode,Param)[/B]
        in
            //--------------------------------------------------------
            // Débug et Exécution du code de la fonction
            //--------------------------------------------------------
            //x
            //Function.Invoke(x,{})       
           // pListParam
           Return

Analyse des paramètres passés à la fonction Function.Invoke

Param=if pListParam is null then
{}
else if pListParam is list then
pListParam
else Text.Split(pListParam, Separator),
On peut soit :
- ne passer aucun paramètre
- passer les paramètres sous forme de liste comme le demande Function.Invoke
- passer les paramètre sous forme texte et le texte est transformé en liste

Remarque
On peut aussi charger puis lancer comme ceci :
x= Load("myTest")() --> le fait de placer ces () traduit cela en fonction à exécuter

let
    /*
            pFunction            as text,
            optional pListParam  as any,
            optional pExtension  as text,
            optional pFolder     as text,
            optional pSep        as text
    */
 
    Return= fnLoadAndRunPQFunction("myTest2","TestList.xlsx;TBRH")
in
    Return

Dans le code ci-dessus on passe donc les paramètres sous forme de texte avec le séparateur ";"

Autre exemple

let
    /*
            pFunction            as text,
            optional pListParam  as any,
            optional pExtension  as text,
            optional pFolder     as text,
            optional pSep        as text
    */
 
    Return= fnLoadAndRunPQFunction("myTest2",{"TestList.xlsx","TBRH"})
in
    Return

Autre exemple

let
    /*
            pFunction            as text,
            optional pListParam  as any,
            optional pExtension  as text,
            optional pFolder     as text,
            optional pSep        as text
    */
 
    Return = fnLoadAndRunPQFunction("myTest4","TestList.xlsx,TBRH,true",".M",null,",")

in
    Return

Autre exemple

let
    /*
            pFunction            as text,
            optional pListParam  as any,
            optional pExtension  as text,
            optional pFolder     as text,
            optional pSep        as text
    */
 
    Return = fnLoadAndRunPQFunction("myTest4",{"TestList.xlsx","TBRH",false},".M",null,null)
in
    Return

Dans la requête ci-dessous on charge une requête afin de retourner une table :
ReturnTable = fnLoadPQAny("TBRH")

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
*/
    ReturnTable = fnLoadPQAny("TBRH")
 
in
    ReturnTable

La requête TBRH.pq

let
    Source = Excel.Workbook(File.Contents("C:\Users\oguru\OneDrive\Documents\TestList.xlsx"), null, true),
    TBRH_Sheet = Source{[Item="TBRH",Kind="Sheet"]}[Data],
    #"En-têtes promus" = Table.PromoteHeaders(TBRH_Sheet, [PromoteAllScalars=true]),
    #"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"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
    #"Type modifié"

Ci-dessous d'autres exemples en termes de syntaxes

let

    xLoaded = fnLoadPQAny("myTest1"),
    Return = Function.Invoke(xLoaded,{})
in
    Return

let

    //----------------------------------------------------------------------------------------
    // Plusieurs formes de lancements
    // 1 - On charge la fonction
    // 2 - On l'exécute
    //----------------------------------------------------------------------------------------
    // ReturnLoaded = fnLoadPQFunction("fnCombineMultiTablesEXCEL_V_Table"),
    //----------------------------------------------------------------------------------------
    // Ces deux formes d'exécutions sont possibles
    //----------------------------------------------------------------------------------------
    // ReturnFunction = Function.Invoke(ReturnLoaded,{"D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\CERTIFICATION\2021",null,"STRAT_CHROME",null,"TB_COMPOSANTS",true})
    // ReturnFunction=ReturnLoaded("D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\CERTIFICATION\2021",null,"STRAT_CHROME",null,"TB_COMPOSANTS",true)

    //----------------------------------------------------------------------------------------
    // On peut aussi la charger puis la lancer à l'issue
    //----------------------------------------------------------------------------------------
    ReturnFunction=fnLoadAndRunPQFunction("fnCombineMultiTablesEXCEL_V_Table",{"D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\CERTIFICATION\2021",null,"STRAT_CHROME",null,"TB_COMPOSANTS",true})
 
in
    ReturnFunction//ReturnLoaded

Appel d'un autre module - fnCombineMultiTablesEXCEL_H_Sheet

let
    ReturnFunction=fnLoadAndRunPQFunction("fnCombineMultiTablesEXCEL_H_Sheet",{
        "D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\$__ComBineSynthese\PQ_FUNCTIONS",
        null,
        "PROJ",
        "Technique" 
        })
in
    ReturnFunction

fnLoadAndRunPQFunction

let
    pPath=fnGetParameter("TB_PARAMS","DOSSIER_CSV"),
    pDelim=fnGetParameter("TB_PARAMS","DELIMITEUR_CSV"),
    pNbCols=fnGetParameter("TB_PARAMS","NBCOLS_CSV"),
    pEncoding=fnGetParameter("TB_PARAMS","ENCODE_CSV"),
    pFilterFile=fnGetParameter("TB_PARAMS","FILTRE_CSV"),
    pExt=fnGetParameter("TB_PARAMS","EXTENSION"),
 
    ReturnFunction=fnLoadAndRunPQFunction("fnGetFromFolderCSV",{
            pPath,
            pDelim,
            pEncoding, null,
            pExt
            })
in
    ReturnFunction

fnLoadAndRunPQFunction(

let

    Return= fnLoadAndRunPQFunction("ReadLoadCSVFile",{"D:\DATA\14__DEVELOPPEMENTS__PQY__LABS\CERTIFICATION\TECHNIC_ARPEGE.csv"})
in
    Return

Cas d'une table via une simple requête

let
    Source = Excel.Workbook(File.Contents("C:\Users\oguru\OneDrive\Documents\TestList.xlsx"), null, true),
    TBRH_Sheet = Source{[Item="TBRH",Kind="Sheet"]}[Data],
    #"En-têtes promus" = Table.PromoteHeaders(TBRH_Sheet, [PromoteAllScalars=true]),
    #"Type modifié" = Table.TransformColumnTypes(#"En-têtes promus",{{"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
    #"Type modifié"

Une requête un peu particulière - ByList

let

    pPath=fnGetParameter("TB_PARAMS","DOSSIER_CSV"),
    pDelim=fnGetParameter("TB_PARAMS","DELIMITEUR_CSV"),  
    pEncoding=fnGetParameter("TB_PARAMS","ENCODE_CSV"),
    pNbCols=fnGetParameter("TB_PARAMS","NBCOLS_CSV"),  
    pList=fnGetParameter("TB_PARAMS","LISTE_FICHIERS_CSV"),
  
    RetFunction = fnLoadPQAny("fnCombineFolderCSVByList"),
    Result=RetFunction(pPath, pDelim, pEncoding, pNbCols, pList)
 

in
   Result

Dans le cas où la fonction appelée fait appel à une fonction qui se trouve dans le dossier des modules (DLL) il est nécessaire de la charger comme suit

- GetCSV=fnLoadPQAny2("fnGetCSV"),

il faut impérativement la charger afin qu'elle soit connue dans le Contexte Powerquery. C'est aussi l'un des petits inconvénients sauf si vous montez cette fonction dans l'environnement PowerQuery pour ne pas la charger dans la fonction principale

let
    //---------------------------------------------------------------------------------
    // Fonction permettant de combiner plusieurs fichiers spécifiques dans un dossier
    // Ce dossier et ces fichiers sont définis dans une table paramètres
    //---------------------------------------------------------------------------------
    // La table des paramètres et le paramètre contenu les fichiers à combiner
    fnCombineFolderCSVByList = (
                        pPath           as text,
                        pDelim          as text,
                        pEncoding       as any,
                        pNbCols         as number,
                        pList           as text,
                        optional ppSep  as text
            ) as table =>
    let
        pSep=if ppSep is null then ";" else ppSep,
        LstFiles=Text.Split(pList,pSep),   // Liste des fichiers à combiner
    //-------------------------------------------------------------------------------------
    // On va boucler sur la liste des fichiers à combiner
    //-------------------------------------------------------------------------------------

    // *-*-*-*-*-*-*-*-*-*-*-*--*-*-*--*-*-*-*-*-*-*-*-
   // A charger impérativement
   //*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*
    GetCSV=fnLoadPQAny2("fnGetCSV"),

   //*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*

    LstCombine=List.Accumulate(
                    LstFiles,            // Initialisation de la boucle sur la liste à traiter
                    #table({},{}),       // Initialisation de l'accumulateur sur une table vide
                    (state,current) =>   // Boucle de traitement pour combiner les fichiers un à un
                        let
                            Source = GetCSV(pPath, current, pNbCols, pDelim, pEncoding),     // Importation du fichier en cours
                            Combine = Table.Combine( {state , Source})  // Combinaison du fichier en cours avec ceux déja combinés dans state
                        in                          
                        Combine   // Ensemble des fichiers combinés
                    )    
in
    LstCombine
in
    fnCombineFolderCSVByList    // Résultat de la fonction