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

1 ACCEPTED SOLUTION
20 REPLIES 20
aneesvvm
Frequent Visitor

Did you get any solution for above issue ?

Sadly not, we moved away from the software provider so I stopped trying 

Jyothishree
Frequent Visitor

Good Evening,

I wonder if anyone will be able to assist with my below query.

We have been working with PowerBI connected to Meraki for the last year which has been working great. But since implementing we have now got a lot more devices introduced to the networks. This means we are now going over the 1000 row limit on the API request.
Can you please suggest us help on the best method to get all the rows in the report instead only 1000 rows

I have tried implementing Python script referring Solved: Python pagination get 8000 devices - The Meraki Community in the existing query which did not solve the issue.  Example query after implementing the python script is as follows.

 

let

    Source =

Json.Document(Web.Contents("https://api.meraki.com/api/v1/networks/########/clients?timespan=2678400&perPage=1000", [Headers=[#"X-Cisco-Meraki-API-Key"="####", #"Content-Type"="application/json", Accept="application/json"]])),

    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "mac", "description", "ip", "ip6", "ip6Local", "user", "firstSeen", "lastSeen", "manufacturer", "os", "recentDeviceSerial", "recentDeviceName", "recentDeviceMac", "ssid", "vlan", "switchport", "usage", "status", "notes", "smInstalled", "groupPolicy8021x"}, {"id", "mac", "description", "ip", "ip6", "ip6Local", "user", "firstSeen", "lastSeen", "manufacturer", "os", "recentDeviceSerial", "recentDeviceName", "recentDeviceMac", "ssid", "vlan", "switchport", "usage", "status", "notes", "smInstalled", "groupPolicy8021x"}),

    Custom1 = Table.AddColumn(#"Expanded Column1", "Organisation", each "Immingham"),

    Custom2 = Table.AddColumn(#"Custom1", "OrgCode", each "IMM"),

    #"Added Index" = Table.AddIndexColumn(Custom2, "Index", 1, 1, Int64.Type),

    #"Sorted Rows" = Table.Sort(#"Added Index",{{"lastSeen", Order.Descending}}),

    #"Run Python script" = Python.Execute("def getNetworksDevices(p_orgid):#(lf)    results = []#(lf)    try: #(lf)        r = requests.get('https://api.meraki.com/api/v1/organizations/######/%s/clients' % (p_orgid),headers={'X-Cisco-Meraki-API-Key': ARG_APIKEY,'Content-Type': 'application/json'} )#(lf)        if r.status_code == 200:#(lf)            raw = r.json()#(lf)            for i in raw:  #(lf)                results.append(i)#(lf)#(lf)            while 'next' in r.links :#(lf)                r = requests.get(r.links['next']['url'],headers={'X-Cisco-Meraki-API-Key': ARG_APIKEY,'Content-Type': 'application/json'} )#(lf)                #print(r.links)#(lf)                raw = r.json()#(lf)                for i in raw:  #(lf)                    results.append(i)#(lf)            #print (len(results))#(lf)        return (results)    #(lf)    except:#(lf)        print('ERROR 02: Unable to contact Meraki cloud')#(lf)        print ('API response: {}'.format(r.status_code))#(lf)",[dataset=#"Sorted Rows"]),

    #"Expanded Value" = Table.ExpandTableColumn(#"Run Python script", "Value", {"id", "mac", "description", "ip", "ip6", "ip6Local", "user", "firstSeen", "lastSeen", "manufacturer", "os", "recentDeviceSerial", "recentDeviceName", "recentDeviceMac", "ssid", "vlan", "switchport", "usage", "status", "notes", "smInstalled", "groupPolicy8021x", "Organisation", "OrgCode", "Index"}, {"Value.id", "Value.mac", "Value.description", "Value.ip", "Value.ip6", "Value.ip6Local", "Value.user", "Value.firstSeen", "Value.lastSeen", "Value.manufacturer", "Value.os", "Value.recentDeviceSerial", "Value.recentDeviceName", "Value.recentDeviceMac", "Value.ssid", "Value.vlan", "Value.switchport", "Value.usage", "Value.status", "Value.notes", "Value.smInstalled", "Value.groupPolicy8021x", "Value.Organisation", "Value.OrgCode", "Value.Index"})

in

    #"Expanded Value"

Thanks in advance.

 

Any particular reason for using Python here?  Why not continue with the native Web.Contents and paginate through the API?

Thank you for your response. Could you elaborate how to use Web.Contents and Paginate it through API as I have no idea how to implement it. Can you please share any relevant links.
Thanks

Hello, Thank you for your response.
I tried the Do-while code for iterations but I do not get any results. The issue could be the Merakki URL doesn't consists of page numbers instead it has a timespan. The codes are as follows - 

(page as number) as table =>
let
    Source = Json.Document(Web.Contents("https://api.meraki.com/api/v1/networks/(Academy ID)/clients?timespan=2678400&perPage=1000" ,[Headers=[#"(Mearkki Header)"="(Organization API Key)",))),
    Data1 = Source{1}[Data],
    RemoveBottom = Table.RemoveLastN(Data1,3)
in
    RemoveBottom




let
    Source = List.Generate( () =>
  [Result = try GetData(1) otherwise null, Page = 1],
  each [Result] <> null,
  each [Result = try GetData([Page]+1) otherwise null, Page = [Page]+1],
  each [Result])
in
    Source

Was wondering is there any way to fetch the complete data when page number is not mentioned in the URL .Thanks in advance.

It will be difficult to assist you further without access to the API

Hello, 
Below is the Documentation and API key

Documentation - https://developer.cisco.com/meraki/api-v1/

API Key = 6bec40cf957de430a6f1f2baa056b99a4fac9ea0
Header = (X-Cisco-Meraki-API-Key)

Thank you

Please don't post your API keys on the internet. 

Hi @lbendlin , 
You are right but the above one is the public API by Meraki which anyone can access.

I don't see any pagination on this API call

 

let
    Source = Json.Document(Web.Contents("https://api.meraki.com/api/v1/organizations", [Headers=[#"X-Cisco-Meraki-API-Key"="6bec40cf957de430a6f1f2baa056b99a4fac9ea0"]])),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name", "url", "api", "licensing", "cloud"}, {"id", "name", "url", "api", "licensing", "cloud"}),
    #"Expanded api" = Table.ExpandRecordColumn(#"Expanded Column1", "api", {"enabled"}, {"api.enabled"}),
    #"Expanded licensing" = Table.ExpandRecordColumn(#"Expanded api", "licensing", {"model"}, {"licensing.model"}),
    #"Expanded cloud" = Table.ExpandRecordColumn(#"Expanded licensing", "cloud", {"region"}, {"cloud.region"}),
    #"Expanded cloud.region" = Table.ExpandRecordColumn(#"Expanded cloud", "cloud.region", {"name"}, {"cloud.region.name"})
in
    #"Expanded cloud.region"

 

The networks call comes back 404 as it probably needs more access.

 

let
    Source = 
//Aspinal Wireless
 Json.Document(Web.Contents("https://api.meraki.com/api/v1/networks/N_1234/clients?timespan=2678400&perPage=1000", [Headers=[#"X-Cisco-Meraki-API-Key"="(API KEY)", #"Content-Type"="application/json", Accept="application/json"]])),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "mac", "description", "ip", "ip6", "ip6Local", "user", "firstSeen", "lastSeen", "manufacturer", "os", "recentDeviceSerial", "recentDeviceName", "recentDeviceMac", "ssid", "vlan", "switchport", "usage", "status", "notes", "smInstalled", "groupPolicy8021x"}, {"id", "mac", "description", "ip", "ip6", "ip6Local", "user", "firstSeen", "lastSeen", "manufacturer", "os", "recentDeviceSerial", "recentDeviceName", "recentDeviceMac", "ssid", "vlan", "switchport", "usage", "status", "notes", "smInstalled", "groupPolicy8021x"}),
    Custom1 = Table.AddColumn(#"Expanded Column1", "Organisation", each "Byron"),
    Custom2 = Table.AddColumn(#"Custom1", "OrgCode", each "BYR"),
    #"Added Index" = Table.AddIndexColumn(Custom2, "Index", 1, 1, Int64.Type),
    #"Filtered Rows" = Table.SelectRows(#"Added Index", each ([ssid] = "OASIS-STUDENTIPAD"))
in
    #"Filtered Rows"

 

Thank you for looking into it.
'perPage' in URL  refers to pagination. https://developer.cisco.com/meraki/api/#!pagination/how-does-pagination-work-in-the-dashboard-api

I still get a 404  when trying https://api.meraki.com/api/v1/networks/N_1234/clients  with the sample key.

mahoneypat
Microsoft
Microsoft

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
Super User

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
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Kudoed Authors