topic Re: List.Generate not working in Power Query

List.Generate not working

AlexisPREVOT — Thu, 19 Aug 2021 12:56:53 GMT

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

Re: List.Generate not working

lbendlin — Fri, 20 Aug 2021 02:12:55 GMT

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.

Re: List.Generate not working

ImkeF — Fri, 20 Aug 2021 07:06:23 GMT

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"

Re: List.Generate not working

AlexisPREVOT — Fri, 20 Aug 2021 07:21:53 GMT

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?

Re: List.Generate not working

ImkeF — Fri, 20 Aug 2021 15:06:48 GMT

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

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"

Re: List.Generate not working

AlexisPREVOT — Tue, 31 Aug 2021 07:52:20 GMT

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

Re: List.Generate not working

AlexisPREVOT — Thu, 02 Sep 2021 05:38:06 GMT

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=*&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]],

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]]))
)

Pagination

Thanks to those who helped me