Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ScottypNZ
Regular Visitor

Power Query List Generate on @odata.nextLink where pagination not available

Hi all, 


I am trying to pull data from Dynamics CRM. I can pull the data via the the below OData.Feed, however this  is not feasible as I am unable to select columns before loading which means refresh is several GB and takes 20 minutes, when all I need is around 10mb.
= OData.Feed("https://crm/xxxxxx/api/data/v8.0/contacts")

There is a second option which is using the Json, and this allows me to select the columns which removed 99% of the data, however this particular API has a number of limitations.

1) 5000k limit
2) No page number listed
3) Offset function does not work
4) Limit function does not work
5) No total record count function

The only way to get the data is via the @odata.nextLink. The above Json link returns two columns of information:
1) Values column - A list of the first 5000 records
2) oData.nextLink column - a link to the next 5000 records.

The first URL or BaseURL is BaseURL =Json.Document(Web.Contents("https://crm/xxxxxx/api/data/v8.0/contacts"))

The second comes back with a huge cookie string.
https://crm/xxxxxx/api/data/v8.0/contacts$skiptoken=%3Ccookie%20pagenumber=%222%22%20pagingcookie=%2...

Essentially I need to write a list generate function that returns the (URL) and the list of values into a table, and does so looping through the oData.nextLink.
The first link is using the URL without the oData the rest is using the oData link which makes coding harder. 
The loop should stop when there is no more oData links.
I have left out the column selection as there are hundreds and not needed for the question. 

I have tried to many methods to list, none of the below, or any youtube video below give me the answer. 


Solved: Passing a parameter to a URL - Microsoft Power BI Community
Solved: Power Query Rest API Paging with @odata.nextLink - Microsoft Power BI Community
Solved: List generation not stopping : pagination without ... - Microsoft Power BI Community

@PhilipTreacy, looks like you have contributed to the above so probably pretty close to the answer

let Source = {1..7},

BaseURL = "https://crm/xxxxxx/api/data/v8.0/contacts",
NextURL = Json.Document(Web.Contents("https://crm/xxxxxx/api/data/v8.0/contacts")) [#"@odata.nextLink"],
NextList = Json.Document(Web.Contents("https://crm/xxxxxx/api/data/v8.0/contacts")) [#"value"],

ToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Renamed = Table.RenameColumns(ToTable,{{"Column1", "Page"}}),
AddedBase = Table.AddColumn(Renamed, "Base" as text, each BaseURL),
AddedLink = Table.AddColumn(AddedBase, "Next" as text, each NextURL),
AddedList = Table.AddColumn(AddedLink, "List" as text, each NextList)
in
AddedList

1 REPLY 1
v-yingjl
Community Support
Community Support

Hi @ScottypNZ ,

If the numbet page is not clear for the API, perhaps you can refer this video which introduces that pagiation for API in Power Query by List.Generate():

How to do Pagination without knowing the number of pages (Part 2) in Power Query | List.Generate 

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors