Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
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.