Forum Discussion

AlexisPREVOT's avatar
AlexisPREVOT
Resolver I
5 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...
Need Help
  • ImkeF's avatar
    ImkeF
    5 years ago

    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's avatar
    AlexisPREVOT
    4 years ago

    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

     

AlexisPREVOT's avatar
AlexisPREVOT
Resolver I
4 years ago

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's avatar
AlexisPREVOT
Resolver I
4 years ago

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