Forum Discussion
List.Generate not working
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"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]]))
)in
Pagination
Thanks to those who helped me
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"
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?
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"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]]))
)in
Pagination
Thanks to those who helped me