oguruma
XLDnaute Occasionnel
Voici un exemple pour les utilisateurs de PowerBI comment on peut réaliser ce montage.
Cf. ceci : https://excel-downloads.com/threads...ent-par-index-classement-par-valeur.20081924/
Sous PowerBI le montage est un peu différent en ce qui concerne la gestion du répertoire courant et des paramètres.
On aurait passer par une table interne en ajoutant des données ou via l'assistant de gestion des paramètres intégré (comme c'est le cas en PowerQuery).
Ici les paramètres sont stockés à l'extérieur dans des fichiers texte style .ini comme sous windows.
Environnement
Les paramètres
Environnement Query de PowerBI
fnGetParam
fnLoadPQ
Ici nous n'avons pas d'autres choix que de forcer cette instruction ParamPathDefaut = CurrentFolderParam,
CurrentFolderParam : déterminé dans l'environnement PowerBI/Query
via cette requête
GetParamWin
fnGetParameterWindows
fnGetParamWindowsFile
Ces deux fonctions ont été présentées dans des post précédents.
La requête pour importer les paramètres
La requête pour importer les data
Et enfin les deux requêtes de classement
Cf. ceci : https://excel-downloads.com/threads...ent-par-index-classement-par-valeur.20081924/
Sous PowerBI le montage est un peu différent en ce qui concerne la gestion du répertoire courant et des paramètres.
On aurait passer par une table interne en ajoutant des données ou via l'assistant de gestion des paramètres intégré (comme c'est le cas en PowerQuery).
Ici les paramètres sont stockés à l'extérieur dans des fichiers texte style .ini comme sous windows.
Environnement
Les paramètres
Environnement Query de PowerBI
fnGetParam
PowerQuery:
let
ParamWin=LoadPQ("fnGetParameter")
in
ParamWinfnLoadPQ
Ici nous n'avons pas d'autres choix que de forcer cette instruction ParamPathDefaut = CurrentFolderParam,
CurrentFolderParam : déterminé dans l'environnement PowerBI/Query
via cette requête
PowerQuery:
let
Source = Csv.Document(File.Contents("D:\DATA\06__PRISE_EN_MAIN_POWERBI_LAB\$__pbiRANKING_01\Params\CurrentFolder.ini"),[ Encoding=1252, QuoteStyle=QuoteStyle.None]),
RenameColumns = Table.RenameColumns(Source,{{"Column1", "PARAM"}}),
PARAM = RenameColumns{0}[PARAM]
in
PARAM
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 = CurrentFolderParam,
ParamExtDefaut = ".pq",
ParamFolderPQ_DLL= "PQ_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)GetParamWin
PowerQuery:
let
ParamWin=LoadPQ("fnGetParameterWindows")
in
ParamWinfnGetParameterWindows
PowerQuery:
let fnGetParameterWindows =
(
pTable as any,
pValSection as text,
pName as text,
optional pSection as text,
optional pParam as text,
optional pVal as text
) =>
let
ParamSource = if pTable is text then
Excel.CurrentWorkbook(){[Name=pTable]}[Content]
else pTable,
SectionWin = if pSection <> null then
pSection
else "SECTION",
Parametre = if pParam <> null then
pParam
else "PARAMETRE",
Valeur = if pVal <> null then
pVal
else "VALEUR",
StrParam="each ( [" & SectionWin & "] = " & """" & pValSection & """" & " and [" & 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
//Parametre
Value
//StrParam
in
fnGetParameterWindowsfnGetParamWindowsFile
PowerQuery:
let
//-----------------------------------------------------------------------------
// Combinaison d'un dossier de fichiers Excel
// On ne récupère que les objets tables - tableaux structurés - Kind = Table
//-----------------------------------------------------------------------------
fnGetParamWindowsFile = (
optional ppFileParam as text,
optional ppExtension as text,
optional ppFolder as text,
optional ppDelimiter as text,
optional pSection as text,
optional pParametre as text,
optional pValeur as text
) as table =>
let
//-----------------------------------------------------------------------------
// Fonction : Construction de la table finale
//-----------------------------------------------------------------------------
BuildFromINI = (
pFile as text,
pFieldSection as text,
pFieldParametre as text,
pFieldValeur as text,
pDelim as text) =>
let
Source = Table.FromColumns({Lines.FromBinary(File.Contents(pFile), null, null, 1252)}),
DelSpace01 = Table.TransformColumns(Source,{{"Column1", Text.Trim, type text}}),
FilterNull01 = Table.SelectRows(DelSpace01, each ([Column1] <> "") and ([Column1] <> null)),
DrillColumns1 = FilterNull01[Column1],
ToTable01 = Table.FromList(DrillColumns1, Splitter.SplitTextByDelimiter(pDelim), null, null, ExtraValues.Error),
FillDown = Table.FillDown(ToTable01,{"Column2"}),
FilterNull02 = Table.SelectRows(FillDown, each ([Column1] <> "") and ([Column1] <> null)),
DelCarEndSection = Table.ReplaceValue(FilterNull02,"]","",Replacer.ReplaceText,{"Column2"}),
TableToSplitt = Table.SplitColumn(DelCarEndSection, "Column1", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
DelSpaces = Table.TransformColumns(TableToSplitt,{{"Column1.1", Text.Trim, type any}, {"Column1.2", Text.Trim, type any}, {"Column2", Text.Trim, type any}}),
ReorgColumns = Table.ReorderColumns(DelSpaces,{"Column2", "Column1.1", "Column1.2"}),
RenColumns = Table.RenameColumns(ReorgColumns,{{"Column2", pFieldSection}, {"Column1.1", pFieldParametre}, {"Column1.2", pFieldValeur}}),
TypeColumnsAny = Table.TransformColumnTypes(RenColumns,{{pFieldSection, type any}, {pFieldParametre, type any}, {pFieldValeur, type any}})
in
TypeColumnsAny,
//-----------------------------------------------------------------------------
// MAIN PROCEDURE
//-----------------------------------------------------------------------------
FIELD_SECTION="SECTION",
FIELD_PARAMETRE="PARAMETRE",
FIELD_VALEUR="VALEUR",
PARAMETRE="Parametres",
SUBFOLDER_PARAMS="Params",
BACK_SLASH="\",
CurrentDirectory = CurrentFolderParam,
// Excel.CurrentWorkbook(){[Name="DOSSIER_COURANT"]}[Content]{0}[Column1],
PARAM_PATH_DEFAULT=CurrentDirectory & BACK_SLASH & SUBFOLDER_PARAMS,
PARAM_EXTENSION_DEFAULT = ".ini",
PARAM_DELIMITER_DEFAULT = "[",
PATH_DEFAULT = PARAM_PATH_DEFAULT & BACK_SLASH,
FieldSection = if pSection <> null then
pSection
else FIELD_SECTION,
FieldParametre = if pParametre <> null then
pSection
else FIELD_PARAMETRE,
FieldValeur = if pValeur <> null then
pSection
else FIELD_VALEUR,
pParam = if ppFileParam <> null then
ppFileParam
else PARAMETRE,
Extension = if ppExtension is null then
PARAM_EXTENSION_DEFAULT
else ppExtension,
PathInitial = if ppFolder <> null then
ppFolder
else PATH_DEFAULT,
Delimiter = if ppDelimiter <> null then
ppDelimiter
else PARAM_DELIMITER_DEFAULT,
PathProcedure = PathInitial & (if Text.End(PathInitial, 1) <> BACK_SLASH then BACK_SLASH else ""),
FileParam = PathProcedure & pParam & Extension,
ToBuild = BuildFromINI(FileParam, FieldSection, FieldParametre, FieldValeur, Delimiter)
in
ToBuild
in
fnGetParamWindowsFileCes deux fonctions ont été présentées dans des post précédents.
La requête pour importer les paramètres
PowerQuery:
let
ParamWin=LoadPQ("fnGetParamWindowsFile"),
Source = ParamWin("ParamsRanking")
in
SourceLa requête pour importer les data
PowerQuery:
let
Source = Excel.Workbook(File.Contents(CurrentFolderParam & "\Ranking.xlsx"), null, true),
TB_FLUX_Table = Source{[Item="TB_FLUX",Kind="Table"]}[Data]
in
TB_FLUX_TableEt enfin les deux requêtes de classement
PowerQuery:
let
//---------------------------------------------------------------------------------------
// On récupère la requête à exécuter
//---------------------------------------------------------------------------------------
Qry = fnGetParamWin(TB_PARAMS,"TB_PARAMS","RUN_QUERY_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
PowerQuery:
let
//---------------------------------------------------------------------------------------
// On récupère la requête à exécuter
//---------------------------------------------------------------------------------------
Qry = fnGetParamWin(TB_PARAMS,"TB_PARAMS","RUN_QUERY_BYVALUE"),
//---------------------------------------------------------------------------------------
// 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,
#"Type modifié" = Table.TransformColumnTypes(ToTable,{{"RANG", type number}})
in
#"Type modifié"
Dernière édition: