cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Rogerh
Helper II
Helper II

Api limit of 1000 records - getting the next 1000 records and so on - Dotdigital, Power BI, Web API

Good afternoon,

 

I am quite new to Power BI but have connected a few datasources.

 

I used the Power BI Service, Web API to connect to Dotdigital. I created a flow for the Contacts - we have over 150k records. However I only get 1000 records back, i belive this is due to their API limitation. (I added the dataflow to PBI Desktop to check and confirm there are only 1000 records total). To get around this issue before I used a script to get the 'next page' and the 'next' (and so on) then compile the list. When I tried this script on this API I got the same 1000 records every time. (I added the flow to PBID and had 1000 unique but 200,000 total records.) 

 

I am looking for a way to get collect the first 1000 records, then the next 1000s until I get all of the contacts. Can anyone please assist with some code I can use or base my research on? Thanks in advance

6 REPLIES 6
mahoneypat
Super User IV
Super User IV

When you see their documentation/syntax, you will likely be able to use $skip or $skiptoken in your API call.  If so, you can first create a List of numbers that increment by 1000 (API limit), convert it to a table, convert the numbers to text, and then add a custom column that concatenates the API call (with $skip or $skiptoken) and the number.  You should then be able to expand the returned tables and have your 150k rows.

 

Example list to start your query  = List.Numbers(0,150,1000)

 

 

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@mahoneypat & @lbendlin 

 

Thank you for your replies so far, can I please have some more guidence to make this work?

 

Dotmailer has replied with the below:

 

The skip parameter should be used in with the select parameter when wanting to iterate through a whole data set. If you want to select the next 1000 records you should set the select parameter to 1000 and the skip parameter to 1000, which will return records 1001 to 2000. You should continue to do this until 0 records are returned to retrieve the whole data set.

 

So in the Power BI service I create a Dataflow - Web API, I put in the URL https://api.dotmailer.com/v2/contacts - add my credentials and connect.

 

This bring back the 1000 records (not in a table yet) and in the Advanced Editor it has the below

 

let
  Source = Json.Document(Web.Contents("https://api.dotmailer.com/v2/contacts"))
in
  Source

 

I can understand what you are saying Mahoneypat but I do not have enough knowledge to know where to go from here.

 

As practice I tried created the list using List.Numbers(0,155,1000) to concatenate the following URL https://api.dotmailer.com/v2/contacts?$skip=[ . I created a table but Power BI didnt reconise List.Numbers. I managed to use GENERATESERIES(1,160000,1000) to give me a list and concatinated. However I do not know how to use this in the API call.

 

What would be my next steps?

 

Additinally I tried calling just  

and
and
and I got the same list of contacts back every time. (I tried other examples with the same results)
 
Do you know what I am missing here?
Thanks in advance

Please this query as an example of how to approach this.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below. 

 

You may still need to work out the web call (you could also try it w/o the square brackets).  If successful, you can hit the expand on the last step of this query and hopefully combine all the JSON.  But a function may be needed to pull your data from each JSON return.  Since I couldn't authenticate, I couldn't check that part.

 

let
    Source = List.Numbers(0,150,1000),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type text}}),
    #"Added Prefix" = Table.TransformColumns(#"Changed Type", {{"Column1", each "[" & _, type text}}),
    #"Added Suffix" = Table.TransformColumns(#"Added Prefix", {{"Column1", each _ & "]", type text}}),
    #"Added Custom" = Table.AddColumn(#"Added Suffix", "WebCalls", each Web.Contents("https://api.dotmailer.com/v2/contacts?$select=[1000]&$skip=" & [Column1]))
in
    #"Added Custom"

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Nice code. The tricky part will be to know when to stop.  The documentation says to look at the number of returned rows and stop when it is zero.

 

Maybe a recursive function would be better?

lbendlin
Super User III
Super User III

You need to check in the Dotdigital API documentation how to specify the offset when you request your data.

Thank you for your reply. I am unable to find the answer in the API documentation so have raised a ticket with them to find out

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors