Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm working with the Hubspot CRM API and when you query for a list of all deals, you only get 100 records at a time, and they want you to send subsequent queries with an "offset" to paginate the results.
For instance, if you send:
https://api.hubapi.com/deals/v1/deal/all?hapikey=demo
at the very end of the query, you see the following JSON:
"hasMore":false "offset":27939158
so, if hasMore is true, the NEXT query should look like this:
https://api.hubapi.com/deals/v1/deal/all?hapikey=demo&offset=27939158
and then, we would want to repeat the process until hasMore comes back with false.
i'm completely new to power bi, so would love to know how to handle this type of query process.
in another language, this would just be do { } while (hasMore == false);
or something like that...
Hello @ImkeF ! Thanks for taking the time to help me:
Here is what I have based on your help:
The relevant urls are in a table named Table2
let
//Table2
Source = #table({"Start", "Finish"}, {{0, 500},{501, 1000},{1001, 1500}, {1501, 2000}}),
//CallFunction
CallFunction = Table.AddColumn(Source, "CallFunction", each Function(Text.From([Start]),Text.From([Finish])))
//Function
Function = (Start as text, Finish as text) =>
let
Source = Json.Document(Web.Contents("https://api.pipedrive.com/v1/organizations?start="&Start&"&limit="&Limit&"&api_token=xxxxxxxxxxxxxxxx")),
data = Source[data],
#"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "company_id", "user_id", "done", "type", "reference_type", "reference_id"})
in
#"Expanded Column1"
I unfortunately get a "Token Comma Expected" Error. Are you quickly able to tell me where I have gone wrong?
My fault, sorry - didn't test the query. Pls try this:
let //Table2 Source = #table({"Start", "Finish"}, {{0, 500},{501, 1000},{1001, 1500}, {1501, 2000}}), //CallFunction CallFunction = Table.AddColumn(Source, "CallFunction", each Function(Text.From([Start]),Text.From([Finish]))), //Function Function = (Start as text, Finish as text) => let Source = Json.Document(Web.Contents("https://api.pipedrive.com/v1/organizations?start="&Start&"&limit="&Finish&"&api_token=xxxxxxxxxxxxxxxx")), data = Source[data], #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "company_id", "user_id", "done", "type", "reference_type", "reference_id"}) in #"Expanded Column1" in CallFunction
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hello again @ImkeF
Something seems to work, but it breaks for values 1500 and beyond, where I get an error.
The error is Expression.Error: We cannot convert the value null to type list.
Details
Value =
Type=Type
Which I don't understand
Further, while the code holds up for values 0-1500, most of the values are null, but when the query is run individually, I get values in the tables.
When I run to advanced query, and expand the tables, this is what shows up:
let //Table2 Source = #table({"Start", "Finish"}, {{0, 499},{500, 999},{1000, 1499}, {1500, 1999}}), //CallFunction CallFunction = Table.AddColumn(Source, "CallFunction", each Function(Text.From([Start]),Text.From([Finish]))), //Function Function = (Start as text, Finish as text) => let Source = Json.Document(Web.Contents("https://api.pipedrive.com/v1/organizations?start="&Start&"&limit="&Finish&"&api_token=xxxxxxxx)), data = Source[data], #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "company_id", "user_id", "done", "type", "reference_type", "reference_id"}) in #"Expanded Column1" in CallFunction
Unfortunately that's a mess now. Please start with this query again. I've reordered the steps to make further expansions easier for you:
let Function = (Start as text, Finish as text) => let Source = Json.Document(Web.Contents("https://api.pipedrive.com/v1/organizations?start="&Start&"&limit="&Finish&"&api_token=xxxxxxxx")), data = Source[data], #"Converted to Table" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "company_id", "user_id", "done", "type", "reference_type", "reference_id"}) in #"Expanded Column1", //Table2 Source = #table({"Start", "Finish"}, {{0, 499},{500, 999},{1000, 1499}, {1500, 1999}}), //CallFunction CallFunction = Table.AddColumn(Source, "CallFunction", each Function(Text.From([Start]),Text.From([Finish]))), //Function #"Expanded CallFunction" = Table.ExpandTableColumn(CallFunction, "CallFunction", {"id", "company_id", "user_id", "done", "type", "reference_type", "reference_id"}, {"CallFunction.id", "CallFunction.company_id", "CallFunction.user_id", "CallFunction.done", "CallFunction.type", "CallFunction.reference_type", "CallFunction.reference_id"}) in #"Expanded CallFunction"
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
With regards to the errors above 1500 you can inject an error-handler like this:
CallFunction = Table.AddColumn(Source, "CallFunction", each try Function(Text.From([Start]),Text.From([Finish])) otherwise #table({"id", "company_id", "user_id", "done", "type", "reference_type", "reference_id"}, {})),
This returns a blank table with your column names in case the webcall fails.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
null returns could be due to wrong field names in the expansion of the record. You could try this:
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", Record.FieldNames(#"Converted to Table"[Column1]{0}) )
this will expand all fields that have the same field names as the record of the first row
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hello!
After reading most of the Thread and a couple of hours of try and error later, frustration and mind block starts to kick in for me.
I am trying to adapt the code samples to request data from Airtable.com, which uses the same Pagination with an offset-Parameter like in the last example. Maybe someone can help me??
To make the reproduction easier, I created a sample database with five entries.
Example how the API works, I am requesting only two records (with the pageSize=2 Parameter) to allow testing with minimal data...
// 20170424221134 // https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO&pageSize=2 { "records": [ { "id": "rec9xyewCK97T01kP", "fields": { "Room": "Garage", "Condition": "Poor", "Priority": "Low" }, "createdTime": "2017-04-24T19:53:01.511Z" }, { "id": "recZmk4a5kmxdfK7O", "fields": { "Condition": "Poor", "Priority": "Medium", "Room": "Kitchen" }, "createdTime": "2015-11-16T22:48:35.000Z" } ], "offset": "itrANA53fD5J9bdLa/recZmk4a5kmxdfK7O" }
The offset-Parameter can be inserted in the next call, to get the next two records:
https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO&pageSize=2&offset=itrANA53fD5J9bdLa/recZmk4a5kmxdfK7O
To request the whole dataset (without pagination), I would perform the following in Power BI
let Source = Json.Document(Web.Contents("https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO")), records = Source[records], #"Converted to Table" = Table.FromList(records, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "fields", "createdTime"}, {"Column1.id", "Column1.fields", "Column1.createdTime"}), #"Expanded Column1.fields" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1.fields", {"Condition", "Priority", "Notes", "Room", "Started?", "Projects"}, {"Column1.fields.Condition", "Column1.fields.Priority", "Column1.fields.Notes", "Column1.fields.Room", "Column1.fields.Started?", "Column1.fields.Projects"}) in #"Expanded Column1.fields"
The thing is, the offset parameter must be valid and cannot be left empty. For the first API-Call, it must not be sent at all.
I tried to bring together this coding and the example coding from earlier:
let Pagination = List.Skip(List.Generate( () => [Last_Key = "", Counter=0], // Start Value each [Last_Key] <> null, // Condition under which the next execution will happen each [ Last_Key = try if [Counter]<=1 then "" else [WebCall][offset] otherwise null,// determine the LastKey for the next execution WebCall = Json.Document(Web.Contents("https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO&pageSize=2&offset="&Last_Key&"")), // retrieve results per call Counter = [Counter]+1// internal counter ], each [WebCall] ),1), #"In Tabelle konvertiert" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Erweiterte Column1" = Table.ExpandRecordColumn(#"In Tabelle konvertiert", "Column1", {"records", "offset"}, {"Column1.records", "Column1.offset"}), #"Erweiterte Column1.records" = Table.ExpandListColumn(#"Erweiterte Column1", "Column1.records"), #"Erweiterte Column1.records1" = Table.ExpandRecordColumn(#"Erweiterte Column1.records", "Column1.records", {"id", "fields", "createdTime"}, {"Column1.records.id", "Column1.records.fields", "Column1.records.createdTime"}) in #"Erweiterte Column1.records1"
The thing is now, that the first API call seems to be executed twice, at least I get the first two values doubled in in the result set.
Is there any way to "initialize" the Last_Key and only pass the offset-Parameter if the Last_Key is not initialized?
I tried this:
let Pagination = List.Skip(List.Generate( () => [Last_Key = "&init", Counter=0], // Start Value each [Last_Key] <> null, // Condition under which the next execution will happen each [ Last_Key = try if [Counter]<=1 then "" else "&offset="[WebCall][offset] otherwise null,// determine the LastKey for the next execution WebCall = Json.Document(Web.Contents("https://api.airtable.com/v0/appiXnlh3PAZ46394/Room%20Assessment?api_key=keyi0umQebbJneGDO&pageSize=2"&Last_Key&"")), // retrieve results per call Counter = [Counter]+1// internal counter ], each [WebCall] ),1),
Which would send an empty "init"-parameter on the first call (the API doesn't mind that), and concatenate the offset-Parameter later. But this only returns the first two entries twice.
Can anyone help me? Thank you very much!!
Best regards
Moritz
It's late, so just a quick idea now & maybe more tomorrow 🙂
You have to put a condition into the step "WebCall", like :
if counter= 0 then ...CallWithoutOffset/LastKey else YourCurrentString
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hello Imke,
thanks for your reply. That did the trick! And the Counter<=1 had to be changed to Counter<1... Thanks a lot!
let Pagination = List.Skip(List.Generate( () => [Last_Key = "init", Counter=0], // Start Value each [Last_Key] <> null, // Condition under which the next execution will happen each [ Last_Key = try if [Counter]<1 then "" else [WebCall][Value][offset] otherwise null,// determine the LastKey for the next execution WebCall = try if [Counter]<1 then Json.Document(Web.Contents("https://api.airtable.com/v0/<api>/Room%20Assessment?api_key=<apikey>&pageSize=2")) else Json.Document(Web.Contents("https://api.airtable.com/v0/<api>/Room%20Assessment?api_key=<apikey>pageSize=2&offset="&Last_Key&"")), // retrieve results per call Counter = [Counter]+1// internal counter ], each [WebCall] ),1), #"In Tabelle konvertiert" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Erweiterte Column1" = Table.ExpandRecordColumn(#"In Tabelle konvertiert", "Column1", {"HasError", "Value"}, {"Column1.HasError", "Column1.Value"}), #"Erweiterte Column1.Value" = Table.ExpandRecordColumn(#"Erweiterte Column1", "Column1.Value", {"records", "offset"}, {"Column1.Value.records", "Column1.Value.offset"}), #"Erweiterte Column1.Value.records" = Table.ExpandListColumn(#"Erweiterte Column1.Value", "Column1.Value.records"), #"Erweiterte Column1.Value.records1" = Table.ExpandRecordColumn(#"Erweiterte Column1.Value.records", "Column1.Value.records", {"id", "fields", "createdTime"}, {"Column1.Value.records.id", "Column1.Value.records.fields", "Column1.Value.records.createdTime"}), #"Erweiterte Column1.Value.records.fields" = Table.ExpandRecordColumn(#"Erweiterte Column1.Value.records1", "Column1.Value.records.fields", {"Condition", "Priority", "Room"}, {"Column1.Value.records.fields.Condition", "Column1.Value.records.fields.Priority", "Column1.Value.records.fields.Room"}) in #"Erweiterte Column1.Value.records.fields"
Hi All,
I just wanted to share some code I wrote to get data out of elastic search using its scan and scroll pagination.
So if you have lots of data in elastic search, you must grab it in batches, and this code does exactly that, perhaps it will be helpful to someone:
let FnGetElasticData = (url as text) as list => let // init params size=10000, // the size of the "chunks" of data that will be returned on each batch scroll = "1m", // the time in minutes elastic search should keep the "context" alive // build url for first fetch operator = if Text.Contains(url, "?") then "&" else "?", initUrl = url & operator & "scroll=" & scroll & "&size=" & Text.From(size), // get first batch of results initSource = Json.Document(Web.Contents(initUrl, [IsRetry=true])), totalResults = initSource[hits][total], // the total number of results to return iterations = Number.IntegerDivide(totalResults, size), // the total number of iterations we need to do // build url for scroll scrollId = initSource[_scroll_id], // the scroll id with wich we will fetch the rest of the results uriParts = Uri.Parts(url), // for getting the host and scheme of the url scrollUrl = uriParts[Scheme] & "://" & uriParts[Host] & "/_search/scroll?scroll=" & scroll & "&scroll_id=" & scrollId, // this will return a scrolled result from elastic search FnGetScrolledPage = (url as text) as list => let response = Json.Document(Web.Contents(url, [IsRetry=true])), data = response[hits][hits] in data, // now loop through all of the iterations and return the data resultsList = List.Generate(()=>[i=0, res=initSource[hits][hits]], // Set inital data each [i] <= iterations, // Keep going until all of the iterations have been done or there is no more data each [i=[i] + 1, res = FnGetScrolledPage(scrollUrl)], // Get next batch of results each [res]) in resultsList in FnGetElasticData
Basically, it will create a function that you can call with the URL for your elasticsearch query and it will return all of the results using paging.
Please note that this code will not work in automatic refreshes using the Power BI Gateway because that expects static URLs, you can get over this pretty easily by changing the code above to include the hard-coded URLs and then scheduled refresh will work.
Hi,
I have a similar issue where by I am trying to retreive from a web api but the pagination is only set to 100.
My data is Xml, but I have noticed that the above discussion is related to json, so not sure if this will affect how the code needs to be written for me.
I need to retreive around 300,000 records but the pagination only returns 100 records at a time.
Here is what the URL looks like:
How can I make it so PBI returns all the records, not just 100 at a time?
Thank you
Mike
Hi Mike,
if the URL for the next page looks like so:
everything you have to do is to create a table with one row per necessary call:
Table.FromColumns({{1..300000/100}})
Add another column where you reference the first column as a parameter/variable to your web call:
Web.Page(Web.Contents("http://example.co.uk/example/feedback-details?from=2016-01-01%2009:00:00&to=2017-05-03%2009:30:00&page="&Text.From([Column1])))
It doesn't matter in what format the result will be returned. Build a function that retrieves one of it and apply it as a next step to every (returned results in each) row.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Hi,
Is there a possibility to have the max value ("300000") taken from a previous action or a variable inteaed of having the entire serie "1..300000/100" hard coded?
The idea would be to execute a call to get the max then create the table and iterate the calls.
Regards.
@ngazelle you can create a function on the query which creates that value(300000). and call that function wherever needed
@ngazelle you can create a function on the query which creates that value(300000). and call that function wherever needed
Yes. I'm currently working on a blogpost to describe exactly this. Will post linke once finished.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
lmkeF,
Thank you so much for being such a great resource - it is truly appreciated!!!
I am in a bit of a predicament... I put together a project for a client that pulls data from Airtable via an API into Excel. I am completely finished with the project, except I just discovered that Airtable is only able to pull 100 records at a time! I have been reading through these posts, but I still can't seem to resolve the pagination issue. There are currently almost a 1,000 records and the number of records will grow. I apologize, I'm a bit of a novice writing code in this language!
I copied and pasted your code directly into my query, but I'm getting an error (see below). What am I doing wrong? I'm supposed to send this in to the client tomorrow morning so I'm hoping you see this soon (crossing fingers)! Thank you again for your expertise!
ERROR: Expression.SyntaxError: Token Comma expected. (See code in red below)
let LINK = "https://api.airtable.com/v0/app1DViZWBL9ehK5X/Financial%20Data?&api_key=INSERT_HERE", Pagination = List.Skip(List.Generate( () => [Table = #table({}, {{}}) ,Pages = 1, Counter=0], // Start Value each Table.RowCount([Table])>0 or [Counter]=0 , // Condition under which the next execution will happen each [ WebCall = Json.Document(Web.Contents(LINK],Query=[page=Text.From([Pages])]])), // retrieve results per call Pages = [Pages]+1, Counter = [Counter]+1,// internal counter Table = try Table.FromRecords(WebCall[events]) otherwise Table.FromList({}) // steps of your further query ] , each [Table] ), 1), #"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error) in #"Converted to Table"
Hi @nerd_in_NE
At a first glance, I am fairly certain there is something wrong with your link, as the page argument is nowhere in it.
The web contents row should look something like this (highlighted in red) :
each [ WebCall = Json.Document(Web.Contents("https://api.airtable.com/v0/app1DViZWBL9ehK5X/Financial%20Data?&api_key=INSERT_HERE&page=1",Query=[page=Text.From([Pages])]])),
However, please keep in mind that the format is highlighy dependent on the API you are calling. (i.e. how would you call the first page in the api? is 'page' even the argument you would need or is it called somehow else?)
Additionally, the following part of the query is also dependent on your API:
Table = try Table.FromRecords(WebCall[events])
More specifically, the [events] name will depend on the name of the record you are extracting (it can be anything, depending on your api)
DBa,
Thank you very much for the quick reply!!!
I was actually able to resolve my issues by utilizing a solution posted by "Mo_re" in the post below.
For convenience, here's the code:
let Pagination = List.Skip(List.Generate( () => [Last_Key = "init", Counter=0], // Start Value each [Last_Key] <> null, // Condition under which the next execution will happen each [ Last_Key = try if [Counter]<1 then "" else [WebCall][Value][offset] otherwise null,// determine the LastKey for the next execution WebCall = try if [Counter]<1 then Json.Document(Web.Contents("https://api.airtable.com/v0/<api>/<endpoint>?api_key=<apikey>")) else Json.Document(Web.Contents("https://api.airtable.com/v0/<api>/<endpoint>?api_key=<apikey>&offset="&Last_Key&"")), // retrieve results per call Counter = [Counter]+1// internal counter ], each [WebCall] ),1) in Pagination
Hi ImkeF,
I'm getting an error message around
&Text.From([Column1]))
Expression.Error:There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?
Regards
Mike
Are you using it within a "Table.AddColumn"-Command?
If yes, please share full query code
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |