In today’s post, we will go over an advanced Power Query technique that will help you to import data from Web REST APIs that implement Cursor-Based Pagination. We will demonstrate this technique on Facebook Graph API v2.6, but you can apply it on any APIs with Cursor-Based Pagination.
If you analyze Facebook data, this is a Must Read blog post!
The technique that you are about to learn, helped me to create the Facebook Post Reactions to Trump and Clinton dashboard, and import 1000 posts for each Presidential candidate (and I could read more posts, of course). If you need to create an Excel or Power BI report which is based on Facebook data, and the out-of-the-shelf Facebook connector is not working properly due to bugs (e.g: here), this blog post is a must read for you. Without it, you will only be able to load 100 records from Facebook Graph API latest versions.
Before we start, here is a short intro on Web Pagination and a Cursor-Based Pagination.
Introduction to Web Pagination
Whenever you use a Web API to obtain large data set from a certain web service, you will usually get the responses in small pieces through paginated response. Each response will include a single page with limit number of records. To obtain the next page, you will usually need to provide the next page number or the offset as a parameter.
A nice and simple example for a REST API that uses static pagination, can be found here. The SWAPI API allows you to extract Star Wars fan data and specify a page number to get any of the paginated 10-records size responses. If you are a Star Wars fan, and missed my earlier blogs here and here, you will find there a .pbix implementation of the static pagination. You can also read Matt Masson’s blog post here for a step by step tutorial of static pagination.
So what is a Cursor-Based Pagination, and why is it different than the static pagination?
According to Facebook (here): “Cursor-based pagination is the most efficient method of paging and should always be used where possible. A cursor refers to a random string of characters which marks a specific item in a list of data. … your app shouldn’t store any older cursors or assume that they will still be valid.”
The tricky part of Cursor-Based Pagination is that you cannot know in advance which strings will be used to point you to the next pages. Each cursor is dynamically created when you get the specific page. As a result, you cannot create in advance a list of pages 1,2,3,4,..100 or a list of offsets, 0,10,20,30,40,…,100 to get all your data. Each page will return a randomly-generated cursor for the next page.
So how can we resolve Cursor-Based Pagination in Power Query?
To resolve this challenge in Power Query, you could consider recursions, to go over each page, then find its cursor and recursively drill down to the next page. A better solution for our challenge is to implement a for-loop using List.Generate. If this is the first time you stumble upon this function, you can read more about List.Generate in my previous blog posts here.
With List.Generate we can iterate over the pages. At each step of the iteration we will extract a page of data, keep the URL that includes the cursor for the next page, and store the results in the next item of the generated list. The following diagram illustrates the flow that is implemented with List.Generate.
The code
The following Power Query (M) expression demonstrates the extraction of 10 pages from Donald Trump’s facebook page (You will need to retrieve an access token from Facebook Graph API Explorer, and paste it below).
let
fbPage = "MicrosoftBI", // You can type your facebook page here
iterations = 10, // Number of iterations
url =
"https://graph.facebook.com/v2.8/" &
fbPage &
"/posts?limit=100&fields=message,created_time" &
"&access_token=[<strong>Your access token here</strong>]",
// FnGetPage is the function that performs an import of single page.
// The page consists of a record with the data and the URL in the
// fields data and next. Other Web APIs hold the data and cursor in different formats
// but the principle is the same.
FnGetOnePage =
(url) as record =>
let
Source = Json.Document(Web.Contents(url)),
data = try Source[data] otherwise null,
next = try Source[paging][next] otherwise null,
res = [Data=data, Next=next]
in
res,
GeneratedList =
List.Generate(
()=>[i=0, res = FnGetOnePage(url)],
each [i]<iterations and [res][Data]<>null,
each [i=[i]+1, res = FnGetOnePage([res][Next])],
each [res][Data]),
#"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column2" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
#"Expanded Column3" = Table.ExpandRecordColumn(#"Expanded Column2", "Column1", {"message", "created_time", "id"}, {"message", "created_time", "id"})
in
#"Expanded Column3"
Wait, the code above was given as an example. There is an easier way to get Facebook posts with Power Query in-the-box Facebook connector. The example above is shared for the sole purpose of demonstration.
The compact version of the code
To reuse the M expression above on a different Facebook related API calls, here is a compact version of the code:
let
iterations = 10, // Number of iterations
url =
"https://graph.facebook.com/v2.6/...", // here goes your Facebook URL, Don't forget the access token
FnGetOnePage =
(url) as record =>
let
Source = Json.Document(Web.Contents(url)),
data = try Source[data] otherwise null,
next = try Source[paging][next] otherwise null,
res = [Data=data, Next=next]
in
res,
GeneratedList =
List.Generate(
()=>[i=0, res = FnGetOnePage(url)],
each [i]<iterations and [res][Data]<>null,
each [i=[i]+1, res = FnGetOnePage([res][Next])],
each [res][Data])
in
GeneratedList
When you successfully apply this expression, you will get a list of lists, as shown in the screenshot below. In List Tools, Transform tab, click ToTable.
Click OK in the following dialog.
Expand Column1 twice, and select the relevant fields you wish to extract.
Finally, you can now see that you have multiple pages from Facebook (In the screenshot below you can see the 1000th row from Trump’s Facebook pages):
Let’s review the main function that we use inside List.Generate
FnGetOnePage =
(url) as record =>
let
Source = Json.Document(Web.Contents(url)),
data = try Source[data] otherwise null,
next = try Source[paging][next] otherwise null,
res = [Data=data, Next=next]
in
res
The line that starts with Json.Document extracts the specific page with the input URL. The next line tries to access the field Source[data] which should contain the actual paginated data. If the response doesn’t contains the page due to an error (usually when there is no more data), we assign null to the key Data. The next line gets the URL for the next page including its cursor string. Again, if there is no further page, we will get an error, and assign a null value to the key Next. Finally, we return a record of [Data=…, Next=…]
Let’s review the List.Generate.
GeneratedList = List.Generate( ()=>[i=0, res = FnGetOnePage(url)], each [i]<iterations and [res][Data]<>null, each [i=[i]+1, res = FnGetOnePage([res][Next])], each [res][Data] )
We initialize the List.Generate state with a record that contains a running index i=0 and a nested record res that holds the first record of FnGetOnePage. The next line defines the condition of the loop. We will continue generating the list as long as the index i is smaller than the value in iterations, and as long as the field Data of record res is not null (When it’s null, there are no further pages to retrieve). The next line defines the incremental step on the state of List.Generate. We increment the index by 1, and fetch the next record res that holds the new page in Data and the next URL with its cursor in Next. Finally, we define the next item in the list, which is embodied in the field Data of record res.
Conclusions
In conclusion, we have learnt how to implement Cursor-Based Pagination using List.Generate to extract large data sets from Web APIs. You can use this technique for a variety of Web APIs that don’t support the simple static approach of specifying page numbers or offsets in the API calls.
The most practical scenario for this technique that I can think of is reading Facebook page insights. In the current state of affairs, Facebook has a bug that prevents you from reading page insights with Power Query and its in-the-box Facebook connector.
To access a .pbix file with this tutorial, please subscribe to DataChant in the form below.
Do you have other scenarios where this technique can help you? Please share with us in the comments below.
Pingback: Iterate through API and extract fields in Power Query in Power BI
Pingback: Excel Integration Part 1: Extract & Shape Marketo Data Using Power Query – Philippe Delle Case 's Blog
Nice Article, It’s working for me.
There is one issue in power bi Schedule Refresh not working for it. it’s give me following error.
“You can’t schedule refresh for this dataset because one or more sources currently don’t support refresh.”
Can you please suggest me where I am wrong..
Regards,
Sandeep
Try the workaround here: https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/
Hi Gil,
I just wanted to say thank you so much for this. So far I have managed to get Facebook API data but it seems not work with this query:
https://graph.facebook.com/v2.10/me?fields=client_pages&access_token=%5BAccess Token Needed]
Is there any reason for this?
Many thanks,
Tanim
There is one improvement that should be made to your List.Generate(…) setup. Once you have reached your iteration limit, you are still fetching one more page from the server prior to deciding not to keep it. This is because the logic flow is generate entry -> test condition -> select entry. A simple solution is to test your iteration requirement during the generate entry step, then return null instead of calling the server. You can then add a requirement to your condition to only keep non-null entries.
HI
I am trying to do the same in Microsoft Graph API but using you query i am getting 1 page duplicated by 10 time. Ita not getting next page data. How i can fix that for graph API?
Can you share the M code here?
Any chance of fixing the code snippets in this article? They don’t seem to be working.
I have some issues with the syntax highlighter WordPress plugin. Turned it off for now. You can now see the code.
It is a very nice article, thank you very much for your time sharing this.
To avoid PowerBI refresh issues, how can we adapt this model using the URL options (eg: https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/) ? Any chance you adapt the code?
Just found this. Huge for me. I’m trying to get data from a source that passes just the “offset” value back. Meaning, the next URL would have to append “&offset=” & offset.
I’m new to Power Query, so I made the assuming that changing this code might work:
res = [Data=data, Next=url & “&offset=” & next]
Ultimately, I’m getting a blank list for queries and am curious what I might need to tweak to make this work? Appreciate any thoughts…
Thank you Al
Can you share the code and API?
Product is called Airtable. It’s a private key, so I can’t share access to the API. The code is pretty much just like yours (minus the one line I changed). See it below.
The end of the JSON contains an offset key that I’m supposed to pass into the next query as “&offset=”. See end of JSON here:
],
offset: “itrmbC9o5iuJU8A7K/reclFVnXSgYsbPzda”
}
let
baseUrl = “https://api.airtable.com/v0”,
baseId = “”,
tableName = “Initiatives”,
apiKey = “”,
iterations = 10,
url = baseUrl & “/” & baseId & “/” & tableName & “/?api_key=” & apiKey,
FnGetOnePage =
(url) as record =>
let
Source = Json.Document(Web.Contents(url)),
data = try Source[data] otherwise null,
next = try Source[paging][next] otherwise null,
res = [Data=data, Next=url & “&offset=” & next]
in
res,
GeneratedList =
List.Generate(
()=>[i=0, res = FnGetOnePage(url)],
each [i]<iterations and [res][Data]null,
each [i=[i]+1, res = FnGetOnePage([res][Next])],
each [res][Data])
in
GeneratedList
Anything else that would be helpful? Really appreciate this…
Thank you for sharing. Can you show the full Json response? Does it has a “data”, “paging” and an underlying “next” inside paging?
In your let statement, I think you should make these changes:
data = try Source[records] otherwise null,
next = try Source[offset] otherwise null,
instead of:
data = try Source[data] otherwise null,
next = try Source[paging][next] otherwise null,
The reason – You don’t have a data and paging->next attributes in your JSON. This is why you get nulls.
Here’s a dump of some test data.
====
{
records: [
{
id: “rec2bfDUkglNP4A9B”,
fields: {
Autonumber: 74,
Notes: “test”
},
createdTime: “2019-03-14T16:09:03.000Z”
},
{
id: “recjGNHQ6X74X1lqP”,
fields: {
Autonumber: 125,
Notes: “100”
},
createdTime: “2019-03-14T16:09:15.000Z”
},
{
id: “recjOBnTWw7a2GAB3”,
fields: {
Autonumber: 11,
Notes: “paging”
},
createdTime: “2019-03-14T16:08:34.000Z”
}
],
offset: “itrkzHHPvpOPBVjrA/recjOBnTWw7a2GAB3”
}
Hey there, having a hard time replying to our thread above for some reason…so had to start this one.
We’re closer! I’m getting the first 100 rows in a list, but not the rest. Here’s the code section with our changes:
FnGetOnePage =
(url) as record =>
let
Source = Json.Document(Web.Contents(url)),
data = try Source[records] otherwise null,
next = try Source[offset] otherwise null,
res = [Data=data, Next=url & “&offset=” & next]
in
res,
Really, really, really appreciate all the help here.
Hi Al
Please send me more details by email. The comments are a challenging media at this stage.
Ooo…take that back. It’s working now. Not sure what was wrong the first time. THANK-YOU! Anything I can do in particular to “pay you back” for your time?
We can discuss it in private 🙂 My email is gilra@datachant.com
Hi Gil,
Thank you so much for this article. It helped me out big time!
After using your code to obtain all the paginated results from a webapi I have pretty much finished my report. And so I ran into the next issue: the data refresh in the Power BI Service. I have read and treid Chris Webb’s advice (https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power-bi/), but when I changed the code to meet his workaround, the parameter is nog longer dynamic, and therefor I get x times the same page……
Here is the code I’m using:
et
url = “https://auvikapi.eu2.my.auvik.com/v1/inventory/device/detail?tenants=xxxxxxxxx”,
Source = Json.Document(Web.Contents(url)),
iterations = Source[meta][totalPages],
FnGetOnePage = (url) as record =>
let
Source = Json.Document(Web.Contents(url)),
data = try Source[data] otherwise null,
next = try Source[links][next] otherwise null,
res = [Data=data, Next=next]
in
res,
GeneratedList =
List.Generate(
()=>[i=0, res = FnGetOnePage(url)],
each [i]<iterations and [res][Data]null,
each [i=[i]+1, res = FnGetOnePage([res][Next])],
each [res][Data]),
#”Converted to Table” = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#”Converted to Table”
This is pretty much a 100% copy of your code, with one exception and that is that I get the number of pages, so that I can limit the number of iterations to this number. Other than that, it’s all your code.
Now my altered code is as follows ( and i actually only changed the Source under FnGetonePage):
let
url = “https://auvikapi.eu2.my.auvik.com/v1/inventory/device/detail?tenants=xxxxxxxxx”,
Source = Json.Document(Web.Contents(url)),
iterations = Source[meta][totalPages],
FnGetOnePage = (url) as record =>
let
Source = Json.Document(Web.Contents(
“https://auvikapi.eu2.my.auvik.com”,
[
RelativePath=”v1/inventory/device/info”,
Query=
[
tenants=”391134359548238589″,
]
]
)),
data = try Source[data] otherwise null,
next = try Source[links][next] otherwise null,
res = [Data=data, Next=next]
in
res,
GeneratedList =
List.Generate(
()=>[i=0, res = FnGetOnePage(url)],
each [i]<iterations and [res][Data]null,
each [i=[i]+1, res = FnGetOnePage([res][Next])],
each [res][Data]),
#”Converted to Table” = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
#”Converted to Table”
By changing the code this way I can now schedule a refresh in the Power BI Service. However, it gives me the same results for each page as the parameter isn’t used any more….
I have been sitting on this issue since this morning and it’s now the middle of the night.. so I’m desparate for your help. How can I keep the parameter for the function while keeping the first part of the url static for the scheduled refresh?
Really hope you can find the time to help me out
Many thanks in advance,
Regards,
Rob van Zutphen
Hi Rob,
When you use Power BI Desktop, do you get the correct results? You can contact me by email gilra@datachant.com
Hi – I love this. I have been using on the desktop version of Powerbi to pull data from Workplace by Facebook and decided to move to a Power BI Dataflow but am getting an error – “Can’t Save dataflow. One or more entities references as dynamic data source.” when I try to save the dataflow entity. Has anyone gotten this to work on a dataflow? Here is my code.
let
iterations = 3, // Number of iterations
url =
“https://graph.facebook.com/v3.3/XXXXX/members/?fields=skills{id,name},email&limit=50&access_token=[accesstoken]”,
FnGetOnePage =
(url) as record =>
let
Source = Json.Document(Web.Contents(url)),
data = try Source[data] otherwise null,
next = try Source[paging][next] otherwise null,
res = [Data=data, Next=next]
in
res,
GeneratedList =
List.Generate(
()=>[i=0, res = FnGetOnePage(url)],
each [i]<iterations and [res][Data]null,
each [i=[i]+1, res = FnGetOnePage([res][Next])],
each [res][Data]),
#”Converted to Table” = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Expanded Column2″ = Table.ExpandListColumn(#”Converted to Table”, “Column1″),
#”Expanded Column1″ = Table.ExpandRecordColumn(#”Expanded Column2”, “Column1”, {“email”, “id”, “skills”}, {“Column1.email”, “Column1.id”, “Column1.skills”}),
#”Expanded Column1.posts” = Table.ExpandRecordColumn(#”Expanded Column1″, “Column1.skills”, {“data”}, {“Column1.skills.data”}),
#”Expanded Column1.posts.data” = Table.ExpandListColumn(#”Expanded Column1.posts”, “Column1.skills.data”),
#”Expanded Column1.posts.data1″ = Table.ExpandRecordColumn(#”Expanded Column1.posts.data”, “Column1.skills.data”, {“id”, “name”}, {“Column1.skills.data.id”, “Column1.skills.data.name”}),
#”Renamed Columns” = Table.RenameColumns(#”Expanded Column1.posts.data1″,{{“Column1.email”, “Email”}, {“Column1.id”, “WPID”}, {“Column1.skills.data.id”, “SkillID”}, {“Column1.skills.data.name”, “SkillName”}})
in
#”Renamed Columns”
Thank you Chip for sharing. AFAIK Dataflows cannot support it yet.
Thanks for this entry, very useful. The only thing I’m not a huge fan of, and the main drawback vs. the iterative method, is that the iterations value is hard-coded, meaning that you presumably have to have a sense of how many pages of data you’ll get, then add some padding on top. It’s something that might break in the future (i.e. you no longer retrieve all results) and in principle I like code that can be left unattended.
That’s why I don’t like APIs with a paging cursor as opposed to explictly returning page number / records per page / total number of pages.
in the URL I use headers which I have to provide in order to auhtenticate. Will that work as well in this solution?
For example having a url like:
url = “https://url.azurewebsites.net/api/Services?name=/project/Projects”,[Headers=[client_id=”1234567890″, client_secret=”12354567890″, devision=”1234567″]]
Some how according to the above solution it should work but no call is being made to the server so it looks like no request is done. Could it have to do something with the url combination with headers which in other queries works perfectly fine.
Thank you in advance!
Kind regards,
Arno
in the URL I use headers which I have to provide in order to auhtenticate. Will that work as well in this solution?
For example having a url like:
url = “https://url.azurewebsites.net/api/Services?name=/project/Projects”,[Headers=[client_id=”1234567890″, client_secret=”12354567890″, devision=”1234567″]]
Some how according to the above solution it should work but no call is being made to the server so it looks like no request is done. Could it have to do something with the url combination with headers which in other queries works perfectly fine.
Thank you in advance!
Kind regards,
Anouar
It should work, but probably only on Power BI Desktop (though Microsoft added the feature to skip test connection, so it is worth checking)
Hi there … can someone help me, please? I have almost the same situation that the code proposes, with the difference that I have an undefined number of iteratios. How can I substitute the part where the iteration is validated [[i]<iterations] for: I have a parameter, like NEXT and PREVIOS called
END_OF_STREAM (boolean) with is true when the end of stream is reached. How can I substitute de logic? Any suggestion? Another way is to cut off the iteration .. Thanks!
Hi Eder,
I’m the exact same situation. Tell me if you find a way with DataFlow. Thanks !
Could Power Automate do this kind of stuff ? Maybe we could create the Entity in the Dataflow based on the downloaded Json files.
Regards
Benjamin
Hi there
I created the function get_records:
the web service returns this result format(i shown the example of 1 result returned for simplicity, in real life there are over 70 k records, with 1000 page size):
{“code”:”success”,”details”:{“output”:”{“Records”:[{“Name”:”Tal”,”Age”:40}],”Count”:1}”}}
(page as number) as record =>
let
params = “apikey=” & apikey”,
url = baseUrl & “?” & params & “&page=” & Number.ToText(page) ,
Source = Json.Document(Web.Contents(url)),
data = try Source[details][output] otherwise null,
test = Json.Document(data),
records = test[Records],
count = test[Count],
res = [Records = records , Count=count]
in
res
and i call this funtion from this iterator:
GeneratedList = List.Generate( ()=>
[i=0, res = get_records(i)],
each [res][Count] 0,
each [i=[i]+1, res = get_records(i,module)],
each [res][Records]
),
What happens here is that for each page few same calls are made to the api
I see that by design each call to the api must be made twice, once to fetch the records and see that the [res][Count] 0 criteria is fullfiled, and then pulling the data again t o save it to the model.
Is there a way to prevent this call duplication?
in the 70,000 records case i would like to make only 70 calls.
Thanks
Do you know in advance the page number to go next? This article is about situations where the next page URL can be generated only in runtime after you paste the results of the current page.
Hi there, thanks!
Well in a manner i do.
I do not know the final number of pages, but i know i can advance 1 page each call i make, until the records returned divided by 200 is either 0 or Has a remainder
so if i have 1000 records
and i can query 200 per api call
then i will make 6 calls, at the 6th ill see i got 0 records so thats when i need to pull out.
Try to see if this blog post by Matt Masson can help: https://www.mattmasson.com/2014/11/iterating-over-an-unknown-number-of-pages-in-power-query/
Hi!
Great article! I found that there already was a comment about data refresh in Power BI service,
How can we adapt this model using the URL options? Web.contents(url) part is not acceptable if you want to do auto refresh.
Thank you!
Thanks Michael. My version is refreshable. Can you download the pbix and check it out?
Hi, thanks for this info!
I need to pass an authorization token in the header which does not seem to work – I get error “We couldn’t authenticate with the credentials provided”. I have tried several methods of pagination and I always end up with the same error. The first query will work, so I know the token and syntax is correct.
The full code is:
let
iterations = 100,
AccessTokenHeader = “CwsAuth Bearer=” & fnGetBearerToken(),
// Uses the Citrix Cloud GET method using the bearer token from the previous POST oauth2/token method
url = “https://” & TenantId & “.xendesktop.net/Citrix/monitor/OData/v4/data/Machines”,
fnGetOnePage =
(url) as record =>
let
Source = Json.Document(Web.Contents(url,
[
Headers=[Authorization=AccessTokenHeader, Customer=”xxxxx”]
])),
data = try Source[value] otherwise null,
next = try Source[#”@odata.nextLink”] otherwise null,
res = [Data=data, Next=next]
in
res,
GeneratedList =
List.Generate(
()=>[i=0, res = fnGetOnePage(url)],
each [i]<iterations and [res][Data]null,
each [i=[i]+1, res = fnGetOnePage([res][Next])],
each [res][Data])
in
GeneratedList
I suggest you break this logic into two separate calls without using List.Generate to confirm that the second call works, and better troubleshoot your logic.
Thank you very much for this article! It helped me a lot!
The difference for me is that I am getting “next” as a hash and not an url. I am new to power query so I have already spent a whole day trying to figure it out.
Could you please help?
here is my request:
“`
let
BaseUrl = “some_url”,
Token = “some_token”,
iterations = 10,
GetPage = (url) as record =>
let Source = Json.Document(Web.Contents(url, [Headers=[ #”Authorization” = “Bearer ” & Token ]])),
data = try Source[transactions] otherwise null,
next = try Source([coursor][next]) otherwise null,
res = [Data=data, Next=next]
in res,
GeneratedList =
List.Generate(
()=>[i=0, res = GetPage(BaseUrl)],
each [i]<iterations and [res][Data]null,
each [i=[i]+1, res = GetPage(BaseUrl & “&cursor=” & [res][Next])],
each [res][Data])
in
GeneratedList
“`
I am getting my first 50 records (auto limit_to is 50) and than it stops. I understand that smth is wrong with the url I am trying to pass to GetPage with cursor – [res][Next] inot being extracted from record but I can’t understand what is wrong.
My api response is almost as yours:
“`
{
“transactions”: [
{
“account_id”: 1244,
“amount”: 194.98,
“id”: 31789256,
}
],
“cursor”: {
“count”: 1,
“total”: 544,
“next”: “d7ff70b23980b3f7786c9f792d84a03564d5e8a4db687aa401a7cb108b0badae”,
“prev”: null
}
}
“`
Hi I tried this so many times but I am getting an empty list. But no error. I checked there is no problem with data and data present in all pages.Should I do any modifications in code or can u just use Source[data],Source[paging][next]
Hi Nivibn,
Can you share this issue in the Power BI community or open a support ticket with Microsoft? My availability is limited and it can take some time to understand what is wrong here.
Is it possible to use the code with Shopify API. Shopify puts their next iteration in the Response header.
I don’t think so. Perhaps using a custom connector.
Thanks for the info, I will look.
Hi Tqn,
Have you been able to connect to Shopify and get all pages?
Gil, any help for the ones struggling with it?
Thank you!
Hi Gil,
This post helped me out plenty. I just have a small issue with setting the break condition.
I made slight modifications to your code to turn it into a function to invoke on a list of Projects, I’d like to set the condition that if the Previous Bookmark = Current Bookmark to terminate the loop. As it currently is, it works ok, it will iterate as many times as is set, then query is set to delete duplicates, but I’d like to see it break automatically.
If you have the time, I’ve not been able to adapt the code to refresh online using the Query= and Relative path=
I’m also posting this on PBI Community just in case.
Thank you in advanced.
Code below –
(ProjectID as text) =>
let
iterations = 20,
Bookmark1 = “0”,
url = “https://field.dalux.com/service/APIv2/FieldRestService.svc/v2.2/Projects/” &
ProjectID &
“/Approvals?key=[PERSONAL KEY]” &
Bookmark1,
FnGetOnePage=
(url) as record =>
let
Source = Json.Document(Web.Contents(url)),
data = try Source[ApprovalsList] otherwise null,
bookmark = try Source[NextBookmark] otherwise null,
res = [Data = data, Bookmark = bookmark]
in
res,
GeneratedList =
List.Generate(
() => [i=0, res = FnGetOnePage(url)],
each [i] < iterations,
each [i=[i]+1, Bookmark1 = [res][Bookmark], res = FnGetOnePage(url)],
each [res][Data]
),
#"Converted to Table" = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"),
#"Expanded Column2" = Table.ExpandRecordColumn(#"Expanded Column1", "Column1", {"CreatedByUser", "CreatedDateTime", "ExtensionsDataList", "InspectionType", "IsDeleted", "LocationList", "Project", "ApprovalID", "ApprovalNumber", "ApprovalRevisionList"}, {"Column1.CreatedByUser", "Column1.CreatedDateTime", "Column1.ExtensionsDataList", "Column1.InspectionType", "Column1.IsDeleted", "Column1.LocationList", "Column1.Project", "Column1.ApprovalID", "Column1.ApprovalNumber", "Column1.ApprovalRevisionList"})
in
#"Expanded Column2"
Hi Ricardo,
Please email me in private if you don’t get help in the community forum.
How can I have access to the pbix file? You said in a comment that it is refreshable but in Power BI Service it is impossible to refresh due to the dynamic source.
nice article!