POWERQUERY :: Les boucles avec List.Generate

[oguruma]

Ce post a pour but de démontrer qu'il est possible de réaliser des boucles de traitements au même titre qu'une boucle For Next en VBA.
Ces boucles permettent de générer des listes en tous genres, et transformer au besoin ces listes en tables.
Pour être sur le même que le Post sur les dates, List.Generate permet de générer des listes de jours, mois, années, etc..
Encore une fois cela montre qu'il n'y a pas de solutions universelles.
L'intérêt de ce post et surtout de montrer comment on utilise List.Generate qui fait peut-être partie des instructions les plus compliquées du langage M au même titre que List.Accumulate permettant de boucler sur des listes.

Le fichier joint montre ces exemples plus d'autres.

Boucle simplifiée

let

    //--------------------------------------------------------------------------------------------------------------
    // Equivalent en VBA ==>
    // For I=1 to 10
    //     debug.print I
    // Next I
    //--------------------------------------------------------------------------------------------------------------
    // On obtient une liste de 1..10
    //--------------------------------------------------------------------------------------------------------------
    Source = List.Generate(
     () => 1,        // Valeur de départ elle s'initialise via une fonction représentée par le signe =>
     each _ <= 10,   // Condition de fin de boucle ; le signe '-' représente la valeur courante
     each _ + 1      // Traitement on ajoute 1 à la valeur courante - Correspond au Next I en VBA avec traitement
)a
in
    Source

let

[B]Ici dans cette boucle on simule en fait un indice via un nom de colonne nomme [i] dans l'exemple[/B]
    //--------------------------------------------------------------------------------------------------------------
    // Equivalent en VBA ==>
    // For I=1 to 10
    //     debug.print I
    // Next I
    //--------------------------------------------------------------------------------------------------------------
    // On obtient une liste de 1..10 via un nom de colonne considéré comme indice
    //--------------------------------------------------------------------------------------------------------------
    Source = List.Generate(
            () => [i=0],
            each [i] < 10,
            each [ i = [i] + 1 ],
            each [i]
    )
in
    Source

Cet exemple très simpliste démontre comment il est possible de réaliser une boucle imbriquée comme on avait deux for next

let

    //--------------------------------------------------------------------------------------------------------------
    // Equivalent en VBA ==>
    // For I=1 to 10
    //     debug.print I
    // Next I
    //--------------------------------------------------------------------------------------------------------------
    // On obtient une liste de 1..10 via un nom de colonne considéré comme indice
    // et boucle imbriquée de 1 à 5
    //--------------------------------------------------------------------------------------------------------------
    Source =  List.Generate(
        ()=>[i=1, j=1, z=0],
        each [i] <= 10,
        each
            if [j] <= 5 then
                [i=[i], j=[j]+1, z=[i] * [j]]
            else
                [i=[i]+1, j=1, z=0],
        each [[i], [j], [z]]
 ),
    ToRecord = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ToTable = Table.ExpandRecordColumn(ToRecord, "Column1", {"i", "j", "z"}, {"Indice I", "Indice J", "Z"})
in
    ToTable

Boucle décroissante comme en VBA

let

    //--------------------------------------------------------------------------------------------------------------
    // Equivalent en VBA ==>
    // For I=10 to 1
    //     debug.print I
    // Next I
    //--------------------------------------------------------------------------------------------------------------
    // On obtient une liste de 10..1
    //--------------------------------------------------------------------------------------------------------------
    Source = List.Generate(
     () => 10,        // Valeur de départ elle s'initialise via une fonction représentée par le signe =>
     each _ > 0,     // Condition de fin de boucle ; le signe '-' représente la valeur courante
     each _ - 1      // Traitement on ajoute 1 à la valeur courante - Correspond au Next I en VBA avec traitement
)
in
    Source

let

[B]Traitement avec création d'une liste personnalisée[/B]
    //--------------------------------------------------------------------------------------------------------------
    // Equivalent en VBA ==>
    // For I=1 to 10
    //     debug.print "Elem n° " & I
    // Next I
    //--------------------------------------------------------------------------------------------------------------
    Source = List.Generate(
     () => 1,        // Valeur de départ elle s'initialise via une fonction représentée par le signe =>
     each _ <= 10,   // Condition de fin de boucle ; le signe '-' représente la valeur courante
     each _ + 1,           // Traitement on ajoute 1 à la valeur courante - Correspond au Next I en VBA avec traitement
     each "Elem n° " & Number.ToText(_)  // On récupère le résultat pour le transformer
     // Ici on concatene une chaine de caractères et une valeur que l'on doit convertir en texte
)
in
    Source

Autre possibilité ajouter un traitement conditionnel dans la boucle

let

    //--------------------------------------------------------------------------------------------------------------
    // Equivalent en VBA ==>
    // For I=1 to 10
    //     if I < 5 then debug.print "...." else debug.print "....."
    // Next I
    //--------------------------------------------------------------------------------------------------------------
    Source = List.Generate(
     () => 1,        // Valeur de départ elle s'initialise via une fonction représentée par le signe =>
     each _ <= 10,   // Condition de fin de boucle ; le signe '-' représente la valeur courante
     each _ + 1,           // Traitement on ajoute 1 à la valeur courante - Correspond au Next I en VBA avec traitement
     each if _ < 5 then "Note égale à " & Number.ToText(_) & " éliminatoire"
          else "Note égale à " & Number.ToText(_) & " admis"
    // On peut récupérer le résultat et appliquer un traitement conditionnel

)
in
    Source

Indice et colonne - traitements non significatifs mis à titre d'exemple

let
    Source = List.Generate(
     () => [x = 1, y = 1, z = 0 ] ,     
      each [x] < 100 ,   

      each [y = [x] + [y],     
            x = [x] +1 ,
            z = [y] ] 

    //  each  [z] * 2   
),
    ToRecord = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ToTable = Table.ExpandRecordColumn(ToRecord, "Column1", {"x", "y", "z"}, {"X", "Y", "Z"})
in
    ToTable

let
    Source = List.Generate(
     () => [x = 1, y = 1, z = 0 ] ,     
      each [x] < 100 ,   

      each [y = [x] + [y],     
            x = [x] +1 ,
            z = [y] ] , 

     each  "[x] = "  & Number.ToText([x]) & " [Y] = " & Number.ToText([y]) & " [Z] = " & Number.ToText([z])
    
)

in
    Source

Générer des mois

let

    ListMois = List.Generate(
     () => 28,      // Seuil pour passer au mois suivant on se base sur février
     each _ < 364,  // Limite pour s'arrêter en fin d'année         
     each  _ + 28 , // Pour passer au mois suivant         
     each Text.Proper(Date.MonthName( _ )) & " " & Number.ToText(Date.Year(DateTime.LocalNow()),"####")
      )   

in
    ListMois

let
    YYYY=Date.Year(DateTime.LocalNow()),
    YYYY10 = YYYY + 10,
    ListMois = List.Generate(
     () => [aa = #date(YYYY,1,1)],
     each Date.Year([aa]) < YYYY10, 
     each  [ aa = Date.AddMonths([aa],1)],
     each Text.Proper(Date.MonthName( [aa] )) & "-" & Number.ToText(Date.Year([aa]))
      )
in
    ListMois

Générer des jours et jongler avec les mois

let
    YYYY_DEBUT=Date.Year(DateTime.LocalNow()),
    YYYY_FIN = YYYY_DEBUT + 5,

    //-----------------------------------------------------------------------------------------------
    // On va boucler sur les nn années calculées
    //-----------------------------------------------------------------------------------------------

    ListMois = List.Generate(       
        // Initialisation de la boucle sur les années avec le constructeur de dates
        //-------------------------------------------------------------------------
        () => [aa = #date(YYYY_DEBUT,1,1)],
        each Date.Year([aa]) < YYYY_FIN, 

        // On ajoute une journée
        //----------------------
        each  [ aa = Date.AddDays([aa],1)],

        // Restitution du résultat avec la date
        //-------------------------------------
        each  Text.Proper(Date.DayOfWeekName( [aa] )) & "-" &
            Number.ToText(Date.Day([aa])) & "-" &
            Text.Proper(Date.MonthName( [aa] )) & "-" &
            Number.ToText(Date.Year([aa]))
      )

in
    ListMois

et un exemple de calendrier très simplifié

let
    //-----------------------------------------------------------------------------------------------
    // Démonstration d'un mini calendrier au travers d'une boucle
    //-----------------------------------------------------------------------------------------------

    YYYY_DEBUT=Date.Year(DateTime.LocalNow()),
    YYYY_FIN = YYYY_DEBUT + 3,

    //-----------------------------------------------------------------------------------------------
    // On va boucler sur les nn années calculées
    //-----------------------------------------------------------------------------------------------

    ListMois = List.Generate(       
        //-------------------------------------------------------------------------
        // Initialisation de la boucle sur les années avec le constructeur de dates
        // Définiton de l'enregistrement date
        //-------------------------------------------------------------------------
        // Initialisation pour le 1er passage de la boucle
        //-------------------------------------------------------------------------
        () => [
                aa = #date(YYYY_DEBUT,1,1),
                jj=Date.Day(aa),
                jjjj=Date.DayOfWeekName(aa),
                mm=Date.Month(aa),
                mmm=Date.MonthName(aa)
              ],

        //-------------------------------
        // Condition de fin de boucle
        //-------------------------------
        each Date.Year([aa]) - 1 < YYYY_FIN, 
        
        //----------------------
        // On ajoute une journée
        //----------------------
        each [   
                 aa = Date.AddDays([aa],1),
                 jj = Date.Day([aa]),
                 jjjj= Date.DayOfWeekName([aa]),
                 mm = Date.Month([aa]),
                 mmm = Date.MonthName([aa])
            ],
        each [[aa], [jjjj], [jj], [mm], [mmm]]
      ),
    ToTableFrom = Table.FromList(ListMois, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ToTable = Table.ExpandRecordColumn(ToTableFrom, "Column1", {"aa", "jjjj", "jj", "mm", "mmm"}, {"aa", "jjjj", "jj", "mm", "mmm"})
in
    ToTable

 

[oguruma]

Bonjour, un petit complément sur la possibilité de faire une boucle imbriquée à deux niveaux

let

    //--------------------------------------------------------------------------------------------------------------
    // Equivalent en VBA ==>
    // For I=1 to 10
    //    For J=1 to 5
    //     ' traitement
    //    Next J
    // Next I

    Source = List.Generate(
            () => [i=1],
                each [i] <= 10,
                each [ i = [i] + 1 ],
                // Boucle de 2eme niveau
                //-----------------------
                each List.Generate (
                    () => [ ii=[i], j=1, k=1 ],
                    each [j] <= 5,
                    each [ j = [j] + 1,
                           // Exemple de traitement
                           //-----------------------
                           k = [j] * [j],
                           // On utilise le ? pour éviter l'erreur quand la valeur n'existe pas à la prochaine itération
                           //--------------------------------------------------------------------------------------------
                           ii=[i]?
                    ],
                    each [[ii] ,[j], [k]]
            )           
    ),
    ToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ToRecord = Table.ExpandListColumn(ToTable, "Column1"),
    ToTable2 = Table.ExpandRecordColumn(ToRecord, "Column1", {"ii", "j", "k"}, {"Column1.ii", "Column1.j", "Column1.k"})
in
    ToTable2