POWERQUERY :: Les boucles avec List.Accumulate

[oguruma]

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

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

La dernière valeur est affichée

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

let
    // -----------------------------------------------------------------------------------------------
    // Dernière valeur affichée sous forme de liste
    // -----------------------------------------------------------------------------------------------
    Source = List.Accumulate(
             {0 .. 10},                                
              "",                         
             ( state, current ) => {current})    
in
    Source

On fait la somme et affichage du résultat en tant que valeur

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

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

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

Les Min et Max

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"

Des sélections sur une colonne d'une table

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

Extraction d'une liste

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

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

Traitement au cas par cas d'une liste de caractères

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

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

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

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

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

 

[oguruma]

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]

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

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