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
La dernière valeur est affichée
Même chose mais le résultat est stocké dans une liste
On fait la somme et affichage du résultat en tant que valeur
Lecture de caractères un à un
Produit cartésien un peu comme un SOMMEPRODUCT sous Excel
Les Min et Max
Des sélections sur une colonne d'une table
Extraction d'une liste
Exploration et création d'une liste
Traitement au cas par cas d'une liste de caractères
Les cumuls de colonnes
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
SourceLa 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
SourceMê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
SourceOn 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
SourceLecture 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
SourceProduit 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
resultLes 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"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
ResultatExtraction 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
ContientExploration 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
LstTraitement 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
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
ResultLes 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
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
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
