oguruma
XLDnaute Occasionnel
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
La fonction maintenant bien connue sur le Forum : fnGetParameter
La fonction RQ_RANK_BYINDEX
La fonction RQ_RANK_BYVALUE
La fonction fnRankingByIndex.pq
La fonction fnRankingByValue.pq
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
PowerQuery:
(
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
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
fnGetParameterLa fonction RQ_RANK_BYINDEX
Code:
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
ToTableLa fonction RQ_RANK_BYVALUE
PowerQuery:
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
RunLa fonction fnRankingByIndex.pq
PowerQuery:
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
fnRankingByIndexLa fonction fnRankingByValue.pq
PowerQuery:
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