POWERQUERY :: RANKING - Classement par index - Classement par valeur

[oguruma]

Suite du RANKING.

Le classement par valeur diffère du classement par index en tenant compte des valeurs égales. Un peu comme sur un podium où il y deux 3ème le suivant est donc 5ème.

On passe toujours par une table paramètres sous Excel

Le dossier actuel est déterminé par la formule : =GAUCHE(CELLULE("nomfichier";$A$19);TROUVE("[";CELLULE("nomfichier";$A$2))-2)

On fait appel à PowerQuery qui se résume à ceci :

l'essentiel du code est sur dans un répertoire dédié

La fonction LoadPQ afin de charger les modules

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

La fonction maintenant bien connue sur le Forum : fnGetParameter

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

La fonction RQ_RANK_BYINDEX

let
    //---------------------------------------------------------------------------------------
    // On récupère la requête à exécuter
    //---------------------------------------------------------------------------------------
    Qry =  fnGetParameter("TB_PARAMS", "RQ_RANKING_BYINDEX"),

    //---------------------------------------------------------------------------------------
    // 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","APPLI","FLUX","RANG")
               else Run
in
    ToTable

La fonction RQ_RANK_BYVALUE

let
    //---------------------------------------------------------------------------------------
    // On récupère la requête à exécuter   
    //---------------------------------------------------------------------------------------
    Qry =  fnGetParameter("TB_PARAMS", "RUN_QUERY_BYVALUE"),

    //---------------------------------------------------------------------------------------
    // On charge le script de la requête en mémoire
    //---------------------------------------------------------------------------------------
  
    Run = LoadPQ(Qry)   
    

in
    Run

La fonction fnRankingByIndex.pq

let
    fnRankingByIndex = (
            pTable              as any,
            pFieldCriteria      as text,
            pFieldValue         as text,
            pFieldRankName      as text
    ) as table =>
    
    let
        
        Source = if pTable is table then
                    pTable
                    else Excel.CurrentWorkbook(){[Name=pTable]}[Content],

        ChangedType = Table.TransformColumnTypes(Source,{{pFieldCriteria, type text}, {pFieldValue, Int64.Type}}),
        SortedRows = Table.Sort(Source,{{pFieldValue, Order.Descending}, {pFieldCriteria, Order.Ascending}}),
        AddedIndex = Table.AddIndexColumn(SortedRows, pFieldRankName, 1, 1)
        
    in
        AddedIndex
in
    fnRankingByIndex

La fonction fnRankingByValue.pq

let
    fnRankingByValue = (
            pTable              as any,
            pFieldCriteria      as text,
            pFieldValue         as text,
            pFieldRankName      as text
        ) as table =>
    
    let
        
        pFieldData="Data",

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

        ListColumns_1=Table.ColumnNames(Source),
        ListColumns_2=List.RemoveMatchingItems(ListColumns_1,{pFieldCriteria}),
        ListColumns_3=List.RemoveMatchingItems(ListColumns_1,{pFieldValue}),
        ListColumns_4=ListColumns_1 & {pFieldRankName},

        ChangedType = Table.TransformColumnTypes(Source,{{pFieldCriteria, type text}, {pFieldValue, Int64.Type}}),
        SortedRows = Table.Sort(Source,{{pFieldValue, Order.Descending}, {pFieldCriteria, Order.Ascending}}),
        AddedIndex = Table.AddIndexColumn(SortedRows, "__Index__", 1, 1),
        GroupedRows = Table.Group(AddedIndex, {pFieldValue}, {{pFieldRankName, each List.Min([__Index__]), type number}, {pFieldData, each _, type table}}),
        ExpandedData = Table.ExpandTableColumn(GroupedRows, pFieldData, ListColumns_3, ListColumns_3),
        ReorderedColumns = Table.ReorderColumns(ExpandedData,ListColumns_4)
        
    in
        ReorderedColumns
in
    fnRankingByValue