Forum Discussion

AlexisPREVOT's avatar
AlexisPREVOT
Resolver I
4 years ago
Solved

List.Generate not working

Hi,

 

I just reuse a power query code so that I can use an API.
I thought it was working but I have a problem using List.Generate.

Indeed, I manage to recover my "cursor" and the function manages to increment on the second one but remains blocked on this one.

For example :

When I call the API, I get a first result with # "header développé" [header.curseurSuivant] = AAAAAA

Then comes the first loop which gives me as a result # "header développé" [header.curseurSuivant] = BBBBBBB

 

And for all my other results my # "header développé" [header.curseurSuivant] is the same and is equal to BBBBBB

 

Here is the code that will be more meaningful to you :

let

Source = Json.Document(Web.Contents("https://api.insee.fr/entreprises/sirene/V3/siren?q=periode(etatAdministratifUniteLegale%3AA)&curseur=*", [Headers=[Authorization="xxxxxx"]])),
#"Converti en table" = Table.FromRecords({Source}),
#"header développé" = Table.ExpandRecordColumn(#"Converti en table", "header", {"statut", "message", "total", "debut", "nombre", "curseur", "curseurSuivant"}, {"header.statut", "header.message", "header.total", "header.debut", "header.nombre", "header.curseur", "header.curseurSuivant"}),

 

data = let

Pagination = ((List.Generate(() => Source,
each #"header développé"[header.curseurSuivant]<>null,
each Json.Document(Web.Contents("https://api.insee.fr/entreprises/sirene/V3/siren?q=periode(etatAdministratifUniteLegale%3AA)&curseur="& List.Last(#"header développé"[header.curseurSuivant]), [Headers=[Authorization="xxxxxxx"]]))
)))

in
Pagination,
#"Converted to Tables" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore)
in
#"Converted to Tables"

 

Is it possible to find a solution for me please?

thanks in advance

Need Help

6 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    You didn't specify the initial value of 

     

    #"header développé"[header.curseurSuivant]

     

    in the seed. Might also want to use a simple name for that variable.

  • ImkeF's avatar
    ImkeF
    Community Champion
    4 years ago

    Hi AlexisPREVOT ,
    this is because you're alway referencing the first step.
    But List.Generate is usually used to reference the previous step of the current iteration.
    If you use the "each" syntax sugar, you have to use the underscore ("_") to reference the result of the previous iteration:

     

    let

    Source = Json.Document(Web.Contents("https://api.insee.fr/entreprises/sirene/V3/siren?q=periode(etatAdministratifUniteLegale%3AA)&curseur...", [Headers=[Authorization="xxxxxx"]])),
    #"Converti en table" = Table.FromRecords({Source}),
    #"header développé" = Table.ExpandRecordColumn(#"Converti en table", "header", {"statut", "message", "total", "debut", "nombre", "curseur", "curseurSuivant"}, {"header.statut", "header.message", "header.total", "header.debut", "header.nombre", "header.curseur", "header.curseurSuivant"}),

     

    data = let

    Pagination = ((List.Generate(() => Source,
    each List.Last( _[header.curseurSuivant] )<>null,
    each Json.Document(Web.Contents("https://api.insee.fr/entreprises/sirene/V3/siren?q=periode(etatAdministratifUniteLegale%3AA)&curseur..."& List.Last( _ [header.curseurSuivant]), [Headers=[Authorization="xxxxxxx"]]))
    )))

    in
    Pagination,
    #"Converted to Tables" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore)
    in
    #"Converted to Tables"

     

    • AlexisPREVOT's avatar
      AlexisPREVOT
      Resolver I
      4 years ago

      Hi ImkeF,

       

      Thanks for your explanations and the code.
      I tried to use it but unfortunately I got an error message telling me that the "header.CurseurSuivant" field of the record could not be found.

      Would you please have an idea?