POWERQUERY :: Tris dynamiques, regroupements et somme

[oguruma]

Bonjour le Forum,
ce post fait suite à celui-ci.
Il démontre qu'il est possible d'arriver aux mêmes résultats en passant par PowerQuery.
Pour des traitements analogues avec une forte volumétrie on évite ainsi d'alourdir le classer Excel avec des formules.
Néanmoins c'est certes un plus complexe. Cela demande une maîtrise de PowerQuery. Avec un peu d'investissement c'est totalement réalisable.

1. Cas du tri multicolonnes

Cela se traduit par la requête suivante. Les colonnes sont passées en dur.
Le tableau peut être mis à jour par le bouton Actualiser.
En VBA :

Sub ACTU06()
    ActiveWorkbook.Connections("Requête - TB_EVOL_TRI_01").Refresh
End Sub

let
    Source = Excel.CurrentWorkbook(){[Name="TB_EVOL"]}[Content],
    #"Lignes triées" = Table.Sort(Source,{{"ETAB", Order.Ascending}, {"EFFECTIF", Order.Ascending}})
in
    #"Lignes triées"

2. Regroupement et somme

let
    Source = Excel.CurrentWorkbook(){[Name="TB_EVOL"]}[Content],
    TBL_REGROUPER = Table.Group(Source, {"ETAB"}, {{"EFFECTIF", each List.Sum([EFFECTIF]), type number}, {"INDICE", each List.Sum([INDICE]), type number}, {"EMPLOI", each List.Sum([EMPLOI]), type number}, {"CLASSIFICATION", each List.Sum([CLASSIFICATION]), type number}}),
    TBL_TRIER = Table.Sort(TBL_REGROUPER,{{"ETAB", Order.Ascending}})
 
in
    TBL_TRIER

3. Choix des colonnes à trier et ordre

Ici ce code est un peu plus complexe. Il faut récupérer les paramètres sélectionnés dans les listes déroulantes. La capture de cellules dans des requêtes PowerQuery a été démontrée dans mes précédents Post à ce propos. En voici une déclinaison.
Sous cette forme

STR_CRIT_1 = Excel.CurrentWorkbook(){[Name="DYN_CRIT_1"]}[Content]{0}[Column1],

let
    Source = TB_EVOL,
    STR_CRIT_1 = Excel.CurrentWorkbook(){[Name="DYN_CRIT_1"]}[Content]{0}[Column1],
    STR_CRIT_2 = Excel.CurrentWorkbook(){[Name="DYN_CRIT_2"]}[Content]{0}[Column1],
    STR_CRIT_3 = Excel.CurrentWorkbook(){[Name="DYN_CRIT_3"]}[Content]{0}[Column1],

    STR_ORDRE_1 = Excel.CurrentWorkbook(){[Name="DYN_ORDRE_1"]}[Content]{0}[Column1],
    STR_ORDRE_2 = Excel.CurrentWorkbook(){[Name="DYN_ORDRE_2"]}[Content]{0}[Column1],
    STR_ORDRE_3 = Excel.CurrentWorkbook(){[Name="DYN_ORDRE_3"]}[Content]{0}[Column1],

    INT_ORDRE_1 = if STR_ORDRE_1="CROISSANT" then 0 else 1,
    INT_ORDRE_2 = if STR_ORDRE_2="CROISSANT" then 0 else 1,
    INT_ORDRE_3 = if STR_ORDRE_3="CROISSANT" then 0 else 1,

    TBL_TRI = Table.Sort(Source,{{STR_CRIT_1, INT_ORDRE_1}, {STR_CRIT_2, INT_ORDRE_2}, {STR_CRIT_3, INT_ORDRE_3}})
   
in
    TBL_TRI

ce type de code

    STR_ORDRE_1 = Excel.CurrentWorkbook(){[Name="DYN_ORDRE_1"]}[Content]{0}[Column1],

permet de remplacer les valeurs de type Enumération de PowerQuery par leur vraie valeur - eg Order.Ascending

4. Somme selon deux critères

let
    Source = TB_EVOL,  
    STR_Fld_1="ETAB",
    STR_Fld_2 = Excel.CurrentWorkbook(){[Name="CRIT_KPI"]}[Content]{0}[Column1],
    CRIT_ETAB = Excel.CurrentWorkbook(){[Name="CRIT_ETAB"]}[Content]{0}[Column1],
    TBL_FILTRE = Table.SelectRows(Source, each (Record.Field(_,STR_Fld_1) = CRIT_ETAB)),
    STR_EvalField="TBL_FILTRE[" & STR_Fld_2 & "]",
    EVAL_Field=Expression.Evaluate(STR_EvalField,[STR_Fld_2=STR_Fld_2, TBL_FILTRE=TBL_FILTRE]),
    LST_SUM=List.Sum(EVAL_Field)
in
    LST_SUM

5. Le TOP(n)

let
    Source = TB_EVOL,
    TOP_N=Excel.CurrentWorkbook(){[Name="TOP_N"]}[Content]{0}[Column1],
    #"Lignes triées" = Table.Sort(Source,{{"EFFECTIF", 1}}),
    #"Conserver les premières lignes" = Table.FirstN(#"Lignes triées",TOP_N)
in
    #"Conserver les premières lignes"

6. Choix des colonnes à afficher avec critère de tri dynamique
Cet exemple est sans doute le plus délicat.
En effet on doit gérer les colonnes à afficher en fonction de celles retenues dans le tableau de paramétrage.

let
    Source = TB_EVOL,

    STR_CRIT_1 = Excel.CurrentWorkbook(){[Name="SEL_COL_DYN_CRIT_1"]}[Content]{0}[Column1],
    STR_ORDRE_1 = Excel.CurrentWorkbook(){[Name="SEL_COL_DYN_ORDRE_1"]}[Content]{0}[Column1],
    INT_ORDRE_1 = if STR_ORDRE_1="CROISSANT" then 0 else 1,
   
    TBL_SORT = Table.Sort(Source,{{STR_CRIT_1, INT_ORDRE_1}}),
 
    TBL_CHOIX_COL = Table.SelectColumns(TBL_SORT,TB_CHOIX_COL)

   
in
    TBL_CHOIX_COL

mais au préalable il est nécessaire de construire une requête TB_CHOIX_COL qui nous fournir la liste des colonnes à retenir

let
    Source = Excel.CurrentWorkbook(){[Name="TB_CHOIX_COL"]}[Content],
    #"Lignes filtrées" = Table.SelectRows(Source, each ([AFFICHE] = "OUI")),
    COLONNE = #"Lignes filtrées"[COLONNE]
in
    COLONNE

 

[merinos-BernardEtang]

Bonjour @oguruma ,


J'avais raté ce post...

Trop bon... Je sais ce que je peux étudier ce soir...