POWERQUERY :: Les boucles avec List.Accumulate

oguruma

XLDnaute Occasionnel
Bonjour, par ce temps... rien de mieux que de poster du PowerQuery :)

Pour boucler "la boucle" sur les boucles en PowerQuery ce post expose quelques possibilités sur la fonction qui n'est pas toujours évidente à mettre en oeuvre : List.Accumulate.
Les exemples choisis sont non significatifs. Ils ont pour but de montrer comment la mettre en application. Pour certains des exemples on peut avoir aussi le même résultat en passant directement par les fonctions List (Max, Min, Last, First, etc.).

Enumération du contenu d'une liste
PowerQuery:
let
    //------------------------------------------------------------------------------------------------
    // Enumération simple de chiffres de 1 à 20
    // -----------------------------------------------------------------------------------------------
    Source = List.Accumulate(
      {0 .. 20},                  
      "",                         
      ( state, current ) =>
      // -------------------------------------------------------------------
      // Test préalable pour éviter l'erreur sur la concaténation de liste
      // ------------------------------------------------------------------
        if state = "" then { current } 
        else if Number.IsEven( current ) then state & { current } else state)    
in
    Source

1704480267985.png


La dernière valeur est affichée
PowerQuery:
let
    // -----------------------------------------------------------------------------------------------
    // La dernière valeur est affichée uniquement soit 10
    // -----------------------------------------------------------------------------------------------
    Source = List.Accumulate(
             {0 .. 10},                                
              "",                         
             ( state, current ) => current)    
in
    Source

Même chose mais le résultat est stocké dans une liste
Code:
let
    // -----------------------------------------------------------------------------------------------
    // Dernière valeur affichée sous forme de liste
    // -----------------------------------------------------------------------------------------------
    Source = List.Accumulate(
             {0 .. 10},                                
              "",                         
             ( state, current ) => {current})    
in
    Source

1704480451082.png


On fait la somme et affichage du résultat en tant que valeur
Code:
let
    //-----------------------------------------------------------------------------------------
    // On affiche la somme de 0 à 10
    //-----------------------------------------------------------------------------------------    
    Source = List.Accumulate(
            {0 .. 10},                                
            0,                         
            ( state, current ) => current + state )          
in
    Source

Lecture de caractères un à un
Code:
let
    //-----------------------------------------------------------------------------------------
    // Concaténation des lettres une à une
    //-----------------------------------------------------------------------------------------
    Source = List.Accumulate(
            {"a","b","c","d"},                                
            "",                         
            ( state, current ) => state & current  )          
in
    Source

// Ou dans l'ordre inverse
let
    //-----------------------------------------------------------------------------------------
    // Concaténation inversée
    //-----------------------------------------------------------------------------------------
    Source = List.Accumulate(
            {"a","b","c","d"},                                
            "",                         
            ( state, current ) =>  current & state )          
in
    Source

Produit cartésien un peu comme un SOMMEPRODUCT sous Excel
Code:
let 
  //----------------------------------------------------------------------
  // Produit cartésien de deux listes
  //----------------------------------------------------------------------
  list1 = { 1, 2, 3 },
  list2 = { 3, 6, 9 }, 
  ziplist = List.Zip( { list1, list2 } ),
  result =  
    List.Accumulate( 
      ziplist, 
      {}, 
      ( state, current ) => state & { List.Product( current ) } 
    )
in 
  result

1704480725450.png


Les Min et Max
Code:
let
    //-----------------------------------------------------------------
    // Détection des min et max et rangement dans un record
    //-----------------------------------------------------------------
    Source =List.Accumulate( { 100, 4900, - 25400, 150000, 600 }, 
                 [ min = 0, max = 0 ],     
                 ( state, current ) =>     
                     [
                       min = if state[min] > current then current else state[min],
                       max = if state[max] < current then current else state[max]
                     ]
),
    #"Converti en table" = Record.ToTable(Source)
in
    #"Converti en table"

1704480809962.png


Des sélections sur une colonne d'une table
Code:
let
    //-----------------------------------------------------------------------------------------------
    // On isole les résultats dans un record
    //-----------------------------------------------------------------------------------------------
    Source = TB_VENTES,
    Select=List.Accumulate
                (
                    Source[Client],
                    [Contient="", ContientPas=""],
                    (state,current)=>
                    [ 
                      Contient = if Text.Contains(current,"c") then if state[Contient] ="" then {current} else state[Contient] & {current} else state[Contient],
                      ContientPas = if not Text.Contains(current,"c") then if state[ContientPas] ="" then {current} else state[ContientPas] & {current} else state[ContientPas]
                    ]
    ),
    ToTable = Record.ToTable(Select),
    Resultat = Table.ExpandListColumn(ToTable, "Value")
in
    Resultat

1704480888722.png


1704480901817.png


Extraction d'une liste
Code:
let
    //----------------------------------------------------------------
    // Création d'une liste spécifique 
    //----------------------------------------------------------------
    Source = TB_VENTES,
    Select=List.Accumulate
                (
                    Source[Client],
                    [Contient="", ContientPas=""],
                    (state,current)=>
                    [ 
                      Contient = if Text.Contains(current,"c") then if state[Contient] ="" then {current} else state[Contient] & {current} else state[Contient],
                      ContientPas = if not Text.Contains(current,"c") then if state[ContientPas] ="" then {current} else state[ContientPas] & {current} else state[ContientPas]
                    ]
    ),
    Contient = Select[Contient]

// ou    ContientPas = Select[ContientPas]
in
    Contient

Exploration et création d'une liste
Code:
let
    //----------------------------------------------------------------
    // Exploration d'une colonne d'une table
    //----------------------------------------------------------------
    Source = TB_VENTES,
    Lst=List.Accumulate
                (
                    Source[Produit],
                    "",
                    (state,current)=> if state="" then {current} else state & {current}
                    
    )
in
    Lst

1704481090935.png


Traitement au cas par cas d'une liste de caractères
Code:
let
    //-------------------------------------------------------------------------------------------------------
    // Cette exemple non signification montre que l'on peut traiter les élements d'une liste au cas par cas
    //------------------------------------------------------------------------------------------------------
    Chaine = Text.ToList("bananaSplit"), 
    Result = List.Accumulate(
        Chaine,
        "",
        (state, current)=> 
            if state="" then {current} 
            else 
                if current = "a" then state & {Text.Upper(current)} else state & {current}
    )
in
    Result

1704481154890.png


Code:
let
    //--------------------------------------------------------------------------------------------------
    // Traitement des éléments d'une liste au cas par cas - on compte des caractères spécifiques
    //--------------------------------------------------------------------------------------------------
    Chaine = Text.ToList("bananaSplit"), 
    Result = List.Accumulate(
        Chaine,
        0,
        (state, current)=> if current = "a" then state + 1 else state
    )
in
    Result

Les cumuls de colonnes
Code:
let 
    //-------------------------------------------------------------------------------
    // List.Accumulate est très pratique pour effectuer des cumuls de colonnes
    //-------------------------------------------------------------------------------
    
    // ------------------------------------------------------------------------------
    // Création d'une table à la volée
    // ------------------------------------------------------------------------------
    Tbl = #table(type table[Montant=Int64.Type],{{5}, {10}, {20}, {25}, {30}}),

    // ------------------------------------------------------------------------------
    // On passe par une table temporaire pour créer l'index    
    // --------------------------------------------------------------------------
    TblTemp = Tbl,
    AddIndex = Table.AddIndexColumn(TblTemp, "Index", 1, 1, Int64.Type),

    // ------------------------------------------------------------------------------
    // Somme cumulée
    // Dans cette méthode on récupère le dernier cumul pour y ajouter la valeur courante en fonction de la valeur de l'index
    // ------------------------------------------------------------------------------
    Cumul = Table.AddColumn(AddIndex, "Cumul",
        each List.Accumulate(Tbl[Montant],
        {0}, // Initialisation sur le 1er élément de la liste
        (state,current)=> 
            state & {List.Last(state)+current}){[Index]},Int64.Type),
            
    SupprColIndex = Table.RemoveColumns(Cumul,{"Index"})
in
    SupprColIndex

1704481249142.png


Code:
let
    //---------------------------------------------------------------------------------------------------------
    // Cumul d'une colonne en passant par un index mais sans List.Accumulate (autre méthode
    //---------------------------------------------------------------------------------------------------------
    Source = Excel.CurrentWorkbook(){[Name="TB_VENTES_2"]}[Content],

    //---------------------------------------------------------------------------------------------------------
    // Ajout d'un index nécessaire à la progression du cumul
    //---------------------------------------------------------------------------------------------------------    
    AddIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),

    //----------------------------------------------------------------------------------------------------------
    // On fait appel à List.Range pour simuler un SOMME($A$2:A2) sous Excel quand on propage la formule
    //----------------------------------------------------------------------------------------------------------
    ListIndex = Table.AddColumn(AddIndex, "LstRange", each List.Range(AddIndex[Montant],0,[Index])),

    //---------------------------------------------------------------------------------------------------------
    // Le List.Accumulate est remplacé par un List.Sum
    //---------------------------------------------------------------------------------------------------------
    Cumul = Table.AddColumn(ListIndex, "Cumul Montant", each List.Sum([LstRange])),

    //---------------------------------------------------------------------------------------------------------
    // Suppression des colonnes temporaires inutiles
    //---------------------------------------------------------------------------------------------------------
    SupprColonnes = 
        Table.RemoveColumns(Cumul,{"LstRange", "Index"})
        
in
    SupprColonnes

1704481352148.png


Code:
let
    //---------------------------------------------------------------------------------------------------------
    // Cumul par List.Accumulate
    //---------------------------------------------------------------------------------------------------------
    Source = Excel.CurrentWorkbook(){[Name="TB_VENTES_2"]}[Content],

    //---------------------------------------------------------------------------------------------------------
    // Index pour pilotage la progression du cumul
    //---------------------------------------------------------------------------------------------------------
    AddIndex = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),    

    //---------------------------------------------------------------------------------------------------------
    // Deux actions en UNE : Un List.Accumulate combiné à un List.Range qui passé en liste
    //---------------------------------------------------------------------------------------------------------
    CumulTotal = Table.AddColumn(AddIndex, "Cumul", 
        each List.Accumulate(
            List.Range(AddIndex[Montant],
                 0,
                [Index]
            ),
            0,
            (state, current)=> state + current)
        ),

    //---------------------------------------------------------------------------------------------------------
    // Suppresion de la colonne index inutile
    //---------------------------------------------------------------------------------------------------------
    SupprColonne = Table.RemoveColumns(CumulTotal,{"Index"})
in
    SupprColonne

1704481398373.png
 

Pièces jointes

  • PQLOOP_ListAccumulate_V0.012.xlsx
    38.9 KB · Affichages: 8

oguruma

XLDnaute Occasionnel
C'est
Bonjour,

je viens de découvrir les List.Accumulate, on peut finalement faire pas mal de chose avec,

En revanche, j'ai un cas particulier et je n'arrive pas à trouver la bonne formule,
j'en ai une qui fonctionne mais qui n'est pas optimisée et ralentie le traitement, le sens de la boucle n'est pas bon :

j'ai une extraction (plusieurs milliers de lignes) dans un fichier texte qui contient des stocks de produits & dans une des colonnes, j'ai un nom d'emplacement.

Dans les emplacements, il y a le nom de la ville ou son code postal, malheureusement pr moi, il y a d'autres informations dans les libellés d'emplacement (info sur l'emplacement et/ou le format du produit) et il y a bien un standard mais différent pour chaque type d'emplacement, ce qui fait que je ne peux pas décomposer les données facilement :

ex :
Temp-Paris-BB
Temp-Paris-SAC
DECLASSE59000
RETOUR75000
Vente-BB2-Lille

je lit cette extraction avec Power Query dans Excel et en améliore l'affichage.

j'ai voulu nettoyer les emplacements pour avoir les villes au propre :
=Table.ReplaceValue(#"étape précédente","Temp-","",Replacer.ReplaceText,{"Ville"})
qui marche très rapidement sur tous les emplacements en une fois, sauf qu'il faut que je le fasse avec une trentaine de chaîne et il est possible que je doive en ajouter d'autre dans le futur !

je me suis donc dit que je pourrais mettre tte ses chaines ("Temp", "BB", ..) à supprimer dans une table à part :
NettoyageEmplacement[Find]

j'ai trouvé cette formule :
= Table.AddColumn(#"étape précédente", "Ville", each List.Accumulate(
List.Numbers(0, Table.RowCount(NettoyageEmplacement)),
[Emplacement],
(A,B) => Text.Replace(A, NettoyageEmplacement[Find]{B},"")))

Sauf que pour moi, le List.Accumulate, travaille sur chaque ligne d'emplacement, une par une, ce qui ralenti le traitement.

j'ai testé de faire un mélange des deux en dupliquant la colonne et en essayant de faire le replacer en boucle sur tte les chaines à supprimer, mais ça tombe en erreur :

= List.Accumulate(NettoyageEmplacement[Find],"",
(state,current)=> Table.ReplaceValue(#"étape précédente",{current},"",Replacer.ReplaceText,{"Ville"}))

quelqu'un aurait une piste ?
Bonjour
La piste : poser la question dans le forum réservé à cela. Là c'est lieu de stockage des Trucs & Astuces. Merci.
 

btil

XLDnaute Nouveau
C'est

Bonjour
La piste : poser la question dans le forum réservé à cela. Là c'est lieu de stockage des Trucs & Astuces. Merci.
désolé, à fond dans les tests, je n'ai pas fait attention ;)
je viens de le déplacer dans : https://excel-downloads.com/threads...e-colonne-a-partir-dune-autre-liste.20080801/


si ça peut aider les autres voici la solution :

j'ai une extraction contenant une qté de produit par [emplacement], qui n'est pas tout à fait standardisé dans les libellés. La solution la plus simple pour obtenir la partie qui m’intéresse est de supprimer certaines chaines de caractère qui sont des préfixe / suffixe générique

j'ai créé une table "NettoyageEmplacement" contenant la colonne "[Find]" & qui liste tous les préfixe, ...

dans ma requête Power Query, de mise en forme de mon extraction, je duplique ma colonne [emplacement] en [ville] & exécute ce traitement

PowerQuery:
= List.Accumulate(NettoyageEmplacement[Find], #"étape précédente", (state, current) => Table.ReplaceValue(state, current, "", Replacer.ReplaceText, {"Ville"}))
 
Dernière édition:

Discussions similaires

Statistiques des forums

Discussions
313 209
Messages
2 096 240
Membres
106 541
dernier inscrit
edf