POWERQUERY :: RANKING - Une méthode

[oguruma]

Bonjour, l'idée est d'effectuer un classement croissant par catégorie. A chaque rupture de la catégorie le classement redémarre à 1. Pour cela un exemple que j'ai eu a traiter dans un projet de suivi de flux sur une migration (ici les exemples sont totalement inventés) :

Nous avons les flux d'une application par serveur, domaine, et canaux. On souhaite les ordonner comme suit

pour obtenir ceci, oublions le VBA bien que cela soit possible et non ouvert aux débutants en programmation.

La solution se trouve derrière PowerQuery.

Au préalable une table de paramètres qui va indiquer où aller chercher le code de la fonction Pwq pour l'exécuter puis une table pour modifier les types dynamiquement.

Espace PowerQuery

On peut remarquer que cela tient en très peu de choses.

Le code d'une fonction que tout le monde connait... enfin je pense.... (de temps à autres elle évolue )

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

Le code qui va permettre de charger la fonction PWQ

(
    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"),
    ParamFolderPQ_DLL= fnGetParameter("TB_PARAMS", "SOUS_DOSSIER_DLL"),
   
    DEFAULT = ParamPathDefaut & "\" & ParamFolderPQ_DLL,
    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
                // Cas en fait si la requête est présente dans l'espace powerquery
                // 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
    //FileProcedure
    //ParamPathDefaut
    ReturnFunction
    //Expression.Evaluate(Text.Replace(pCodeName, ".", "_"), #shared)
     //Expression.Evaluate(Text.FromBinary(Binary.Buffer(File.Contents(FileProcedure))), #shared)

et le code qui va lancer la fonction de Ranking sur le critère voulu -

let
    //---------------------------------------------------------------------------------------
    // On récupère la requête à exécuter
    // La requête à exécuter est dans le paramètre "RUN_QUERY"
    //---------------------------------------------------------------------------------------
    Qry =  fnGetParameter("TB_PARAMS", "RUN_QUERY"),

    //---------------------------------------------------------------------------------------
    // On charge le script de la requête en mémoire
    //---------------------------------------------------------------------------------------
 
    Run = LoadPQ(Qry),  
   
    //---------------------------------------------------------------------------------------
    // On exécute le script de la requête chargée
    // Si la requête attend des paramètres, on passe les paramètres en arguments
    //---------------------------------------------------------------------------------------
    ToTable=if Run is function then
               Run("TB_FLUX_APPLIS","DOMAINE","NBR FLUX","ORDRE")
               else Run
in
    ToTable

il suffit de construire les autres requêtes en intervenant dans cette ligne

Run("TB_FLUX_APPLIS","DOMAINE","NBR FLUX","ORDRE") pour remplacer DOMAINE par SERVEUR ou par CANAL

Le code de la fonction de Ranking

let
    fnExpandTableRanking = (
            pTable                       as any,
            pFieldAscending              as text,
            pFieldDescending             as text,
            optional ppFieldIndex        as text
        ) =>

        let
           
            pFieldData="Data",          
            pFieldPartioned="Partitioned",

            pFieldIndex=if ppFieldIndex is null then
                           "Index"
                           else ppFieldIndex,

            Source = Table.Buffer(if pTable is table then
                        pTable
                        else Excel.CurrentWorkbook(){[Name=pTable]}[Content]),

            ChangedType = Table.TransformColumnTypes(Source,{{pFieldAscending, type text}, {pFieldDescending, type number}}),

           
            SortedRows =  Table.Sort(ChangedType,{  
                                                {pFieldAscending, Order.Ascending},
                                                {pFieldDescending, Order.Descending}                                              
                                            }
                                        ),

            GroupedRows = Table.Group(SortedRows, {pFieldAscending}, {{pFieldData, each _, type table}}),                    
            AddedCustom = Table.AddColumn(GroupedRows, pFieldPartioned, each Table.AddIndexColumn([Data], pFieldIndex, 1, 1)),
            RemovedColumns = Table.RemoveColumns(AddedCustom,{pFieldData}),
            DrillDownPartioned=Table.Column(RemovedColumns,pFieldPartioned),
            ListColumnsFromTableFirstRow=Table.ColumnNames(DrillDownPartioned{0}),
            DeleteFieldAscending=  List.RemoveMatchingItems(ListColumnsFromTableFirstRow,{pFieldAscending}),
            ExpandToTable = Table.ExpandTableColumn(RemovedColumns, pFieldPartioned, DeleteFieldAscending, DeleteFieldAscending),
            TransformType=LoadPQ("fnTransformTypes"),
            ToTable=Table.TransformColumnTypes(ExpandToTable,TransformType("TB_TYPES"))
        in
            ToTable
in
    fnExpandTableRanking

[B]et voir le fichier .zip qui rassemble le tout ![/B]