APi with cursor based pagination

Question

Hi,&nbsp;I try to use an API who gives me different information of french entreprises.&nbsp;I read the different API instructions to obtain a pagination cursor and from the site https://datachant.com/2016/06/27/cursor-based-pagination-power-query/ to get all the results.&nbsp;So I wrote the following code:letiterations = 10,url = "https://api.insee.fr/entreprises/sirene/V3/siren?q=periode(etatAdministratifUniteLegale%3AA)&amp;curseur=*",FnGetOnePage =(url) as record =&gt;letSource = Json.Document(Web.Contents(url, [Headers=[Authorization="XXXXXXX"]])),data = try Source[header] otherwise null,next = try Source[header.curseurSuivant],res = [Data=data, Next=next]inres,GeneratedList =List.Generate(()=&gt; [i=0, res=FnGetOnePage(url)],each [i]&lt;iterations and [res][Data]&lt;&gt;null,each [i=[i]+1, res = FnGetOnePage([res][Next])],each [res][Data]),inGeneratedList&nbsp;However the query only gives the first page, I cannot implement the iterations.I think my concern is that my first API call requires "cursor = *" at the end of the API but I can't figure out how to make this work.Can you please help me?&nbsp;if that can help with the resolution, here is the response of the header provided by the API and the result returned by PowerBi:&nbsp;Header of API :&nbsp;Result of PowerBi :&nbsp;

v-easonf-msft · Answer

Hi.&nbsp;&nbsp;AlexisPREVOT&nbsp;
You can check if these threads could help.
Cursor-Paginated-data-from-Web-API&nbsp;
Web-based-Cursor-Based-Pagination-issues&nbsp;
Cursor-Paginated-data-from-Web-API-with-List-type-outcome&nbsp;
How-to-make-List-Generate-work-with-cursor-based-pagination&nbsp;
Best Regards,Community Support Team _ Eason
&nbsp;

alexisprevot · Answer

Hi,Thank you for your response.Unfortunately I have already read these threads without success.While digging yesterday, it seems to me that the problem is that my next cursor does not come from the same table as my data. When I "just" call the API, I get this:&nbsp;In the header is my next cursor and in UnitesLegales is the data I want to recover&nbsp;

alexisprevot · Answer

Hi&nbsp;v-easonf-msft&nbsp;,I realized that it was possible that I get the value of my next cursor by working the table differently. It would almost work.Here is the code :&nbsp;letSource = Json.Document(Web.Contents("https://api.insee.fr/entreprises/sirene/V3/siren?q=periode(etatAdministratifUniteLegale%3AA)&amp;curseur=*", [Headers=[Authorization="XXXXXXXXXX"]])),#"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"}),nextpage = #"header développé"[header.curseurSuivant],#"unitesLegales développé" = Table.ExpandListColumn(#"header développé", "unitesLegales"),#"unitesLegales développé1" = Table.ExpandRecordColumn(#"unitesLegales développé", "unitesLegales", {"siren", "statutDiffusionUniteLegale", "dateCreationUniteLegale", "sigleUniteLegale", "sexeUniteLegale", "prenom1UniteLegale", "prenom2UniteLegale", "prenom3UniteLegale", "prenom4UniteLegale", "prenomUsuelUniteLegale", "pseudonymeUniteLegale", "identifiantAssociationUniteLegale", "trancheEffectifsUniteLegale", "anneeEffectifsUniteLegale", "dateDernierTraitementUniteLegale", "nombrePeriodesUniteLegale", "categorieEntreprise", "anneeCategorieEntreprise", "periodesUniteLegale"}, {"unitesLegales.siren", "unitesLegales.statutDiffusionUniteLegale", "unitesLegales.dateCreationUniteLegale", "unitesLegales.sigleUniteLegale", "unitesLegales.sexeUniteLegale", "unitesLegales.prenom1UniteLegale", "unitesLegales.prenom2UniteLegale", "unitesLegales.prenom3UniteLegale", "unitesLegales.prenom4UniteLegale", "unitesLegales.prenomUsuelUniteLegale", "unitesLegales.pseudonymeUniteLegale", "unitesLegales.identifiantAssociationUniteLegale", "unitesLegales.trancheEffectifsUniteLegale", "unitesLegales.anneeEffectifsUniteLegale", "unitesLegales.dateDernierTraitementUniteLegale", "unitesLegales.nombrePeriodesUniteLegale", "unitesLegales.categorieEntreprise", "unitesLegales.anneeCategorieEntreprise", "unitesLegales.periodesUniteLegale"}),#"unitesLegales.periodesUniteLegale développé" = Table.ExpandListColumn(#"unitesLegales développé1", "unitesLegales.periodesUniteLegale"),#"unitesLegales.periodesUniteLegale développé1" = Table.ExpandRecordColumn(#"unitesLegales.periodesUniteLegale développé", "unitesLegales.periodesUniteLegale", {"dateFin", "dateDebut", "etatAdministratifUniteLegale", "changementEtatAdministratifUniteLegale", "nomUniteLegale", "changementNomUniteLegale", "nomUsageUniteLegale", "changementNomUsageUniteLegale", "denominationUniteLegale", "changementDenominationUniteLegale", "denominationUsuelle1UniteLegale", "denominationUsuelle2UniteLegale", "denominationUsuelle3UniteLegale", "changementDenominationUsuelleUniteLegale", "categorieJuridiqueUniteLegale", "changementCategorieJuridiqueUniteLegale", "activitePrincipaleUniteLegale", "nomenclatureActivitePrincipaleUniteLegale", "changementActivitePrincipaleUniteLegale", "nicSiegeUniteLegale", "changementNicSiegeUniteLegale", "economieSocialeSolidaireUniteLegale", "changementEconomieSocialeSolidaireUniteLegale", "caractereEmployeurUniteLegale", "changementCaractereEmployeurUniteLegale"}, {"unitesLegales.periodesUniteLegale.dateFin", "unitesLegales.periodesUniteLegale.dateDebut", "unitesLegales.periodesUniteLegale.etatAdministratifUniteLegale", "unitesLegales.periodesUniteLegale.changementEtatAdministratifUniteLegale", "unitesLegales.periodesUniteLegale.nomUniteLegale", "unitesLegales.periodesUniteLegale.changementNomUniteLegale", "unitesLegales.periodesUniteLegale.nomUsageUniteLegale", "unitesLegales.periodesUniteLegale.changementNomUsageUniteLegale", "unitesLegales.periodesUniteLegale.denominationUniteLegale", "unitesLegales.periodesUniteLegale.changementDenominationUniteLegale", "unitesLegales.periodesUniteLegale.denominationUsuelle1UniteLegale", "unitesLegales.periodesUniteLegale.denominationUsuelle2UniteLegale", "unitesLegales.periodesUniteLegale.denominationUsuelle3UniteLegale", "unitesLegales.periodesUniteLegale.changementDenominationUsuelleUniteLegale", "unitesLegales.periodesUniteLegale.categorieJuridiqueUniteLegale", "unitesLegales.periodesUniteLegale.changementCategorieJuridiqueUniteLegale", "unitesLegales.periodesUniteLegale.activitePrincipaleUniteLegale", "unitesLegales.periodesUniteLegale.nomenclatureActivitePrincipaleUniteLegale", "unitesLegales.periodesUniteLegale.changementActivitePrincipaleUniteLegale", "unitesLegales.periodesUniteLegale.nicSiegeUniteLegale", "unitesLegales.periodesUniteLegale.changementNicSiegeUniteLegale", "unitesLegales.periodesUniteLegale.economieSocialeSolidaireUniteLegale", "unitesLegales.periodesUniteLegale.changementEconomieSocialeSolidaireUniteLegale", "unitesLegales.periodesUniteLegale.caractereEmployeurUniteLegale", "unitesLegales.periodesUniteLegale.changementCaractereEmployeurUniteLegale"}),#"Type modifié" = Table.TransformColumnTypes(#"unitesLegales.periodesUniteLegale développé1",{{"header.statut", Int64.Type}, {"header.message", type text}, {"header.total", Int64.Type}, {"header.debut", Int64.Type}, {"header.nombre", Int64.Type}, {"header.curseur", type text}, {"header.curseurSuivant", type text}, {"unitesLegales.siren", Int64.Type}, {"unitesLegales.statutDiffusionUniteLegale", type text}, {"unitesLegales.dateCreationUniteLegale", type date}, {"unitesLegales.sigleUniteLegale", type any}, {"unitesLegales.sexeUniteLegale", type text}, {"unitesLegales.prenom1UniteLegale", type text}, {"unitesLegales.prenom2UniteLegale", type text}, {"unitesLegales.prenom3UniteLegale", type text}, {"unitesLegales.prenom4UniteLegale", type any}, {"unitesLegales.prenomUsuelUniteLegale", type text}, {"unitesLegales.pseudonymeUniteLegale", type any}, {"unitesLegales.identifiantAssociationUniteLegale", type any}, {"unitesLegales.trancheEffectifsUniteLegale", type text}, {"unitesLegales.anneeEffectifsUniteLegale", Int64.Type}, {"unitesLegales.dateDernierTraitementUniteLegale", type datetime}, {"unitesLegales.nombrePeriodesUniteLegale", Int64.Type}, {"unitesLegales.categorieEntreprise", type text}, {"unitesLegales.anneeCategorieEntreprise", Int64.Type}, {"unitesLegales.periodesUniteLegale.dateFin", type date}, {"unitesLegales.periodesUniteLegale.dateDebut", type date}, {"unitesLegales.periodesUniteLegale.etatAdministratifUniteLegale", type text}, {"unitesLegales.periodesUniteLegale.changementEtatAdministratifUniteLegale", type logical}, {"unitesLegales.periodesUniteLegale.nomUniteLegale", type text}, {"unitesLegales.periodesUniteLegale.changementNomUniteLegale", type logical}, {"unitesLegales.periodesUniteLegale.nomUsageUniteLegale", type any}, {"unitesLegales.periodesUniteLegale.changementNomUsageUniteLegale", type logical}, {"unitesLegales.periodesUniteLegale.denominationUniteLegale", type text}, {"unitesLegales.periodesUniteLegale.changementDenominationUniteLegale", type logical}, {"unitesLegales.periodesUniteLegale.denominationUsuelle1UniteLegale", type any}, {"unitesLegales.periodesUniteLegale.denominationUsuelle2UniteLegale", type any}, {"unitesLegales.periodesUniteLegale.denominationUsuelle3UniteLegale", type any}, {"unitesLegales.periodesUniteLegale.changementDenominationUsuelleUniteLegale", type logical}, {"unitesLegales.periodesUniteLegale.categorieJuridiqueUniteLegale", Int64.Type}, {"unitesLegales.periodesUniteLegale.changementCategorieJuridiqueUniteLegale", type logical}, {"unitesLegales.periodesUniteLegale.activitePrincipaleUniteLegale", type text}, {"unitesLegales.periodesUniteLegale.nomenclatureActivitePrincipaleUniteLegale", type text}, {"unitesLegales.periodesUniteLegale.changementActivitePrincipaleUniteLegale", type logical}, {"unitesLegales.periodesUniteLegale.nicSiegeUniteLegale", Int64.Type}, {"unitesLegales.periodesUniteLegale.changementNicSiegeUniteLegale", type logical}, {"unitesLegales.periodesUniteLegale.economieSocialeSolidaireUniteLegale", type text}, {"unitesLegales.periodesUniteLegale.changementEconomieSocialeSolidaireUniteLegale", type logical}, {"unitesLegales.periodesUniteLegale.caractereEmployeurUniteLegale", type text}, {"unitesLegales.periodesUniteLegale.changementCaractereEmployeurUniteLegale", type logical}}),data = letPagination = ((List.Generate(() =&gt; #"Type modifié", //Start Valueeach nextpage&lt;&gt;null, // condition de bouclageeach Json.Document(Web.Contents("https://api.insee.fr/entreprises/sirene/V3/siren?q=periode(etatAdministratifUniteLegale%3AA)&amp;curseur="&amp; nextpage), [Headers=[Authorization="Bearer 93a322ee-09a3-371b-989e-2cd21a531b4d"]]))))inPagination,#"Converted to Tables" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Ignore)in#"Converted to Tables"&nbsp;But of course I have a new error telling me that it is not possible to apply the operator &amp; to the Text and List types here: Json.Document (Web.Contents ("https: //api.insee. en / companies / siren / V3 / siren? q = period (stateAdministrativeUniteLegale% 3AA) &amp; cursor = "&amp; nextpage)&nbsp;How I can resolve it please ?&nbsp;

v-easonf-msft · Answer

Hi,&nbsp;AlexisPREVOT&nbsp;
I'm sorry, I'm not familiar with this.
If the problem is still not resolved, it is recommended to open a support ticket to let engineers look into the issue on your side.
&nbsp;
Best Regards,Community Support Team _ Eason

anonymous · Answer

did you eventually get a solution, I am currently in the situation now and need advice

Forum Discussion

APi with cursor based pagination

5 Replies

Related content

Web based cursor pagination issue

API REST Tracket Cursor Based Pagination

Web-based Cursor-Based Pagination issues

Dashboards based on Paginated reports

Paginated Report