List.Generate not working

[AlexisPREVOT]

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

[ImkeF · Accepted solution]

Oh yes, that was stupid from me. Delete the "header."-part from the strings:

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"}),

data = let

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

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

[ImkeF]

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]

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?

[ImkeF · Accepted solution]

Oh yes, that was stupid from me. Delete the "header."-part from the strings:

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"}),

data = let

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

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

[AlexisPREVOT]

Hi @ImkeF 

Apologies for the delay in responding, but I was on vacation last week.
I tried your solution this morning but unfortunately I still have the same error message. I do not really know what else to do...
@v-yingjl  I'm sorry but my problem is still not resolve

[AlexisPREVOT · Accepted solution]

Hi,

I have found a solution at this problem.

I think the code is not necessarily clean but it works.
if that can help people here is the code:

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

curseurSuivant=#"header développé"[curseurSuivant]],

each List.Last( _[curseurSuivant] )<>null,

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

curseurSuivant=#"header développé"[curseurSuivant]]))
)

in

Pagination

Thanks to those who helped me

[lbendlin]

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.