Thus far in my series of posts using hockey data to explore the subject of data analytics in Power BI (which began all the way back here), I have been focused on “using” the data, and was generally refreshing data whenever I opened the PBIX file. I didn’t set up a refresh schedule for the published versions of the hockey datasets that are embedded into previous posts, and was thinking that someone might come along some day and wonder why the data is old. So today I decided to fix that situation and set up a refresh schedule.
There’s just one problem. I’m using the Web.Contents() function in several queries to get the JSON data from the NHL API.
Actually, the function is not the problem. It’s the way I’m using it to dynamically construct URLs so that I can iterate through lists of players or teams or whatever to retrieve the data I want.
Clearly that technique works just fine because I have data. However, when I try to schedule the refresh in the Power BI service, I get the dreaded error: “You can’t schedule refresh for this dataset because one or more sources currently don’t support refresh.”
I explained how to solve this particular problem once upon a time. For the hockey data queries, I had to use variations on that theme to get the dynamic URLs to work. To do this, I had to fix up my queries (which are actually functions) in the Query Editor by opening up the Advanced Editor and fixing the code as noted below.
getGames and getScores
The first few lines for each of these functions looked like this originally:
(season as text) as table =>
let
Source = Json.Document(Web.Contents("https://statsapi.web.nhl.com/api/v1/schedule?season=" & season)),
I changed to this:
(seasonValue as text) as table =>
let
Source = Json.Document(Web.Contents("https://statsapi.web.nhl.com/api/v1/schedule?season=20172018",
[Query=[season=seasonValue]])),
I had to hard-code a single season into the URL and then the value in the Query argument replaces the hard-coded value when the function is called.
I also had to change the variable for the query argument from season to seasonValue. When I tried Query=[season=season], the function worked independently. I could enter a parameter and invoike the function to see results correctly.
However, when I called the function from another query, I got the following error message: “Operation is not valid due to the current state of the object.” Not exactly a lot of information to work with that from a troubleshooting point of view. That’s when I tried changing season=season to season=seasonValue, the error message went away, and everything worked nicely.
getRoster
I changed this:
Source = Json.Document(Web.Contents("https://statsapi.web.nhl.com/api/v1/teams/"
& teamid & "/roster"))
To this:
Source = Json.Document(Web.Contents("https://statsapi.web.nhl.com/api/v1/teams/",
[RelativePath= teamid & "/roster"]
))
Here I use the RelativePath argument to append a string (generated dynamically) to the URL in the first argument.
getPlayer
I replaced this:
Source = Json.Document(Web.Contents("https://statsapi.web.nhl.com/api/v1/people/" & playerid))
With this:
Source = Json.Document(Web.Contents("https://statsapi.web.nhl.com/api/v1/people",
[RelativePath= "/" &playerid ]
)),
))
In this case, it’s the same idea as the change I made in getRoster except here I had to put the static string first and follow it with the variable.
getYearlyPlayerStatistics
I went from this:
Source = Json.Document(Web.Contents("http://statsapi.web.nhl.com/api/v1/teams/"& teamid & "?hydrate=roster(person(stats(splits=yearByYear)))"))
To this:
Source = Json.Document(Web.Contents("http://statsapi.web.nhl.com/api/v1/teams",
[RelativePath=teamid & "?hydrate=roster(person(stats(splits=yearByYear)))"]))
Technically hydrate is a query parameter, but it’s static in this case (unlike seasonValue in the getGames example). Therefore, I could just append it to the relative path argument’s value.
One more thing…
I also had hard-coded the season start years from 1917 to 2017 into the scores away and scores home queries. I don’t want to have to update this range of values each year, so I adjusted these queries also.
Instead of this:
Source = {1917..2017}
I did this:
Source = {1917..Date.Year(DateTime.LocalNow())}
Voila!
After I publish the PBIX file to the cloud, I need to update the dataset credentials and set a refresh schedule. To do this, I perform the following steps:
- Open the Power BI web page, locate the dataset, point to it to display the ellipsis icon, and then click the ellipsis.
- Click Schedule Refresh.
- In the Data Source Credentials section, click the Edit Credentials link and click Sign In. Repeat for each link.
And… Houston, we have a problem…
Each of the links for credentials is the URL for the functions that Power BI is attempting to resolve before it allows me to schedule the refresh. Everything is fine except for the getPlayer function which is trying this URL: https://statsapi.web.nhl.com/api/v1/people.
The problem in this case is that the API returns an error message because it requires a player ID. Which the function actually returns, but it doesn’t have one when Power BI is trying it out. Because the player ID is not sent as a query parameter, I can’t hard-code a value and then override it in the Query argument for the Web.Contents() function. Stumped…
Well, this leads me to change up my priority list for working with hockey data. I’ve been thinking recently that refreshing the PBIX file is slower than I would like and it’s only going to get worse as more data is available from the NHL. I want to use the data with other tools besides Power BI, so maybe it’s time to move the data into another repository. Stay tuned!
9 Comments
[…] Stacia Varga shows off Power BI automated refresh when hitting dynamically generated endpoint URLs: […]
Stacia
I am very close to finally make dynamic pagination but I am stuck with refreshing in the service. I am not sure what I need to supply relativepath or query.
This works fine on desktop
(page as number) as table =>
let
Source = Json.Document(Web.Contents(“https://support.xxxxx.com/helpdesk/tickets/view/1000189523?format=json&page=”& Number.ToText(page),[Headers=[Authorization=”xxxxx”, #”Content_Type”=”application/json”]])),
#”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Expanded Column1″ = Table.ExpandRecordColumn(#”Converted to Table”, “Column1”, {“cc_email”, “created_at”, “deleted”, “department_id_value”, “display_id”, “due_by”, “email_config_id”, “frDueBy”, “fr_escalated”, “group_id”, “id”, “isescalated”, “owner_id”, “priority”, “requester_id”, “responder_id”, “source”, “spam”, “status”, “subject”, “ticket_type”, “to_email”, “updated_at”, “description”, “description_html”, “status_name”, “requester_status_name”, “priority_name”, “source_name”, “requester_name”, “responder_name”, “to_emails”, “department_name”, “assoc_problem_id”, “assoc_change_id”, “assoc_change_cause_id”, “assoc_asset_id”, “custom_field”}, {“Column1.cc_email”, “Column1.created_at”, “Column1.deleted”, “Column1.department_id_value”, “Column1.display_id”, “Column1.due_by”, “Column1.email_config_id”, “Column1.frDueBy”, “Column1.fr_escalated”, “Column1.group_id”, “Column1.id”, “Column1.isescalated”, “Column1.owner_id”, “Column1.priority”, “Column1.requester_id”, “Column1.responder_id”, “Column1.source”, “Column1.spam”, “Column1.status”, “Column1.subject”, “Column1.ticket_type”, “Column1.to_email”, “Column1.updated_at”, “Column1.description”, “Column1.description_html”, “Column1.status_name”, “Column1.requester_status_name”, “Column1.priority_name”, “Column1.source_name”, “Column1.requester_name”, “Column1.responder_name”, “Column1.to_emails”, “Column1.department_name”, “Column1.assoc_problem_id”, “Column1.assoc_change_id”, “Column1.assoc_change_cause_id”, “Column1.assoc_asset_id”, “Column1.custom_field”}),
#”Expanded Column1.cc_email” = Table.ExpandRecordColumn(#”Expanded Column1″, “Column1.cc_email”, {“cc_emails”, “fwd_emails”, “reply_cc”, “tkt_cc”}, {“Column1.cc_email.cc_emails”, “Column1.cc_email.fwd_emails”, “Column1.cc_email.reply_cc”, “Column1.cc_email.tkt_cc”}),
#”Expanded Column1.cc_email.tkt_cc” = Table.ExpandListColumn(#”Expanded Column1.cc_email”, “Column1.cc_email.tkt_cc”),
#”Expanded Column1.cc_email.reply_cc” = Table.ExpandListColumn(#”Expanded Column1.cc_email.tkt_cc”, “Column1.cc_email.reply_cc”),
#”Expanded Column1.cc_email.fwd_emails” = Table.ExpandListColumn(#”Expanded Column1.cc_email.reply_cc”, “Column1.cc_email.fwd_emails”),
#”Expanded Column1.cc_email.cc_emails” = Table.ExpandListColumn(#”Expanded Column1.cc_email.fwd_emails”, “Column1.cc_email.cc_emails”),
#”Expanded Column1.custom_field” = Table.ExpandRecordColumn(#”Expanded Column1.cc_email.cc_emails”, “Column1.custom_field”, {“level_2_12133”, “level_3_12133”, “preferred_method_of_contact_12133”, “best_time_to_contact_12133”, “phone_number_12133”, “level_1_12133”, “office_location_12133”, “job_number_12133”}, {“Column1.custom_field.level_2_12133”, “Column1.custom_field.level_3_12133”, “Column1.custom_field.preferred_method_of_contact_12133”, “Column1.custom_field.best_time_to_contact_12133”, “Column1.custom_field.phone_number_12133”, “Column1.custom_field.level_1_12133”, “Column1.custom_field.office_location_12133”, “Column1.custom_field.job_number_12133″}),
#”Sorted Rows” = Table.Sort(#”Expanded Column1.custom_field”,{{“Column1.display_id”, Order.Ascending}})
in
#”Sorted Rows”
I have another query to invoke the parameter
let
Source = List.Generate( ()=>
[Result=try APIView(1) otherwise null, page=1],
each [Result] null,
each[Result=try APIView([page]+1) otherwise null, page=[page]+1],
each [Result]),
#”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Expanded Column1″ = Table.ExpandTableColumn(#”Converted to Table”, “Column1”, {“Column1.cc_email.cc_emails”, “Column1.cc_email.fwd_emails”, “Column1.cc_email.reply_cc”, “Column1.cc_email.tkt_cc”, “Column1.created_at”, “Column1.deleted”, “Column1.department_id_value”, “Column1.display_id”, “Column1.due_by”, “Column1.email_config_id”, “Column1.frDueBy”, “Column1.fr_escalated”, “Column1.group_id”, “Column1.id”, “Column1.isescalated”, “Column1.owner_id”, “Column1.priority”, “Column1.requester_id”, “Column1.responder_id”, “Column1.source”, “Column1.spam”, “Column1.status”, “Column1.subject”, “Column1.ticket_type”, “Column1.to_email”, “Column1.updated_at”, “Column1.description”, “Column1.description_html”, “Column1.status_name”, “Column1.requester_status_name”, “Column1.priority_name”, “Column1.source_name”, “Column1.requester_name”, “Column1.responder_name”, “Column1.to_emails”, “Column1.department_name”, “Column1.assoc_problem_id”, “Column1.assoc_change_id”, “Column1.assoc_change_cause_id”, “Column1.assoc_asset_id”, “Column1.custom_field.level_2_12133”, “Column1.custom_field.level_3_12133”, “Column1.custom_field.preferred_method_of_contact_12133”, “Column1.custom_field.best_time_to_contact_12133”, “Column1.custom_field.phone_number_12133”, “Column1.custom_field.level_1_12133”, “Column1.custom_field.office_location_12133”, “Column1.custom_field.job_number_12133”}, {“Column1.Column1.cc_email.cc_emails”, “Column1.Column1.cc_email.fwd_emails”, “Column1.Column1.cc_email.reply_cc”, “Column1.Column1.cc_email.tkt_cc”, “Column1.Column1.created_at”, “Column1.Column1.deleted”, “Column1.Column1.department_id_value”, “Column1.Column1.display_id”, “Column1.Column1.due_by”, “Column1.Column1.email_config_id”, “Column1.Column1.frDueBy”, “Column1.Column1.fr_escalated”, “Column1.Column1.group_id”, “Column1.Column1.id”, “Column1.Column1.isescalated”, “Column1.Column1.owner_id”, “Column1.Column1.priority”, “Column1.Column1.requester_id”, “Column1.Column1.responder_id”, “Column1.Column1.source”, “Column1.Column1.spam”, “Column1.Column1.status”, “Column1.Column1.subject”, “Column1.Column1.ticket_type”, “Column1.Column1.to_email”, “Column1.Column1.updated_at”, “Column1.Column1.description”, “Column1.Column1.description_html”, “Column1.Column1.status_name”, “Column1.Column1.requester_status_name”, “Column1.Column1.priority_name”, “Column1.Column1.source_name”, “Column1.Column1.requester_name”, “Column1.Column1.responder_name”, “Column1.Column1.to_emails”, “Column1.Column1.department_name”, “Column1.Column1.assoc_problem_id”, “Column1.Column1.assoc_change_id”, “Column1.Column1.assoc_change_cause_id”, “Column1.Column1.assoc_asset_id”, “Column1.Column1.custom_field.level_2_12133”, “Column1.Column1.custom_field.level_3_12133”, “Column1.Column1.custom_field.preferred_method_of_contact_12133”, “Column1.Column1.custom_field.best_time_to_contact_12133”, “Column1.Column1.custom_field.phone_number_12133”, “Column1.Column1.custom_field.level_1_12133”, “Column1.Column1.custom_field.office_location_12133”, “Column1.Column1.custom_field.job_number_12133″})
in
#”Expanded Column1”
It works seamlessly in the desktop but when I try to refresh on the service it fails. I tried to tweak the code
(page as number) as table =>
let
Source = Json.Document(Web.Contents(“https://xxxxx.swinerton.com/helpdesk/tickets/view/1000189524?format=json&page=”& Number.ToText(page),[Headers=[Authorization=”xxxxxx”, #”Content_Type”=”application/json”]],
[Query=(page)])),
#”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Expanded Column1″ = Table.ExpandRecordColumn(#”Converted to Table”, “Column1”, {“cc_email”, “created_at”, “deleted”, “department_id_value”, “display_id”, “due_by”, “email_config_id”, “frDueBy”, “fr_escalated”, “group_id”, “id”, “isescalated”, “owner_id”, “priority”, “requester_id”, “responder_id”, “source”, “spam”, “status”, “subject”, “ticket_type”, “to_email”, “updated_at”, “description”, “description_html”, “status_name”, “requester_status_name”, “priority_name”, “source_name”, “requester_name”, “responder_name”, “to_emails”, “department_name”, “assoc_problem_id”, “assoc_change_id”, “assoc_change_cause_id”, “assoc_asset_id”, “custom_field”}, {“Column1.cc_email”, “Column1.created_at”, “Column1.deleted”, “Column1.department_id_value”, “Column1.display_id”, “Column1.due_by”, “Column1.email_config_id”, “Column1.frDueBy”, “Column1.fr_escalated”, “Column1.group_id”, “Column1.id”, “Column1.isescalated”, “Column1.owner_id”, “Column1.priority”, “Column1.requester_id”, “Column1.responder_id”, “Column1.source”, “Column1.spam”, “Column1.status”, “Column1.subject”, “Column1.ticket_type”, “Column1.to_email”, “Column1.updated_at”, “Column1.description”, “Column1.description_html”, “Column1.status_name”, “Column1.requester_status_name”, “Column1.priority_name”, “Column1.source_name”, “Column1.requester_name”, “Column1.responder_name”, “Column1.to_emails”, “Column1.department_name”, “Column1.assoc_problem_id”, “Column1.assoc_change_id”, “Column1.assoc_change_cause_id”, “Column1.assoc_asset_id”, “Column1.custom_field”}),
#”Expanded Column1.cc_email” = Table.ExpandRecordColumn(#”Expanded Column1″, “Column1.cc_email”, {“cc_emails”, “fwd_emails”, “reply_cc”, “tkt_cc”}, {“Column1.cc_email.cc_emails”, “Column1.cc_email.fwd_emails”, “Column1.cc_email.reply_cc”, “Column1.cc_email.tkt_cc”}),
#”Expanded Column1.cc_email.tkt_cc” = Table.ExpandListColumn(#”Expanded Column1.cc_email”, “Column1.cc_email.tkt_cc”),
#”Expanded Column1.cc_email.reply_cc” = Table.ExpandListColumn(#”Expanded Column1.cc_email.tkt_cc”, “Column1.cc_email.reply_cc”),
#”Expanded Column1.cc_email.fwd_emails” = Table.ExpandListColumn(#”Expanded Column1.cc_email.reply_cc”, “Column1.cc_email.fwd_emails”),
#”Expanded Column1.cc_email.cc_emails” = Table.ExpandListColumn(#”Expanded Column1.cc_email.fwd_emails”, “Column1.cc_email.cc_emails”),
#”Expanded Column1.custom_field” = Table.ExpandRecordColumn(#”Expanded Column1.cc_email.cc_emails”, “Column1.custom_field”, {“level_2_12133”, “level_3_12133”, “preferred_method_of_contact_12133”, “best_time_to_contact_12133”, “phone_number_12133”, “level_1_12133”, “office_location_12133”, “job_number_12133”}, {“Column1.custom_field.level_2_12133”, “Column1.custom_field.level_3_12133”, “Column1.custom_field.preferred_method_of_contact_12133”, “Column1.custom_field.best_time_to_contact_12133”, “Column1.custom_field.phone_number_12133”, “Column1.custom_field.level_1_12133”, “Column1.custom_field.office_location_12133”, “Column1.custom_field.job_number_12133″}),
#”Sorted Rows” = Table.Sort(#”Expanded Column1.custom_field”,{{“Column1.display_id”, Order.Ascending}})
in
#”Sorted Rows”
When I enter any number for the page paramert, I see this error
“An error occured in the query. Expression Error:3 arguments were passed to the function which expects between 1 and 2
I can’t test your code because you are accessing a private data source. However, I can take a guess at how to structure your query to get the service to refresh your query.
First, I would have your parameter that generates the page number return a text value to make things easier when you want to use the results. Then start your query like this:
(pageNumber as text) =>
let
Source = Json.Document(Web.Contents(“https://xxxxx.swinerton.com/helpdesk/tickets/view/1000189524?format=json&page=1,[Headers=[Authorization=”xxxxxx”, #”Content_Type”=”application/json”]],
[Query=[format=json&page=pageNumber]])),
The trick here is that you need the query portion of the Web.Contents call to use a hard-coded page number so that it can resolve the call. Then in the query argument you provide the code – with the variable that changes dynamically. Power BI uses that query argument to replace the entire query of the URL (the part after the ?).
I don’t think you need to do this prior to the let keyword:
(pageNumber as text) as table =>
But I’d have to test it out to know for sure. Only you can do that. In other words, I think you can drop the “as table”.
Also if you are copying/pasting from the code that I used, make sure to double-check the double quotes to make sure they are straight up and down and not curled, else you might see a problem when executing the code. If you do a have a problem with the double quotes – try typing one in Notepad and then copy/paste from there.
Hello Stacia,
I followed this wonderful blogspot and created a query as following which works fine in PBI Desktop. I am trying to do a reverse geocoding by following the mapbox documentation- https://docs.mapbox.com/api/search/#reverse-geocoding. I have built the custom function which I am invoking on a column that has Longitude,Latitude Text.
GetCountry = let
Source = (address as text)=>let
Source = Json.Document(Web.Contents(“https://api.mapbox.com/geocoding/v5/mapbox.places?access_token=pk.eyJ1Ijoic21wYTAxIiwiYSI6ImNqdDdpd3dqYzBxMW80NW1tdGh0OWYwZncifQ.Za4e9HphoyT7_lxUhFn4eA”,[RelativePath =”/”&address&”.json”, Query=[access_token=”pk.eyJ1Ijoic21wYTAxIiwiYSI6ImNqdDdpd3dqYzBxMW80NW1tdGh0OWYwZncifQ.Za4e9HphoyT7_lxUhFn4eA”]])),
#”Converted to Table” = Record.ToTable(Source),
Value = #”Converted to Table”{2}[Value],
#”Converted to Table1″ = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Expanded Column1″ = Table.ExpandRecordColumn(#”Converted to Table1″, “Column1”, {“id”, “type”, “place_type”, “relevance”, “properties”, “text”, “place_name”, “bbox”, “center”, “geometry”, “context”}, {“Column1.id”, “Column1.type”, “Column1.place_type”, “Column1.relevance”, “Column1.properties”, “Column1.text”, “Column1.place_name”, “Column1.bbox”, “Column1.center”, “Column1.geometry”, “Column1.context”}),
#”Filtered Rows” = Table.SelectRows(#”Expanded Column1″, each Text.Contains([Column1.id], “country”)),
#”Removed Other Columns” = Table.SelectColumns(#”Filtered Rows”,{“Column1.place_name”})
in
#”Removed Other Columns”
in
Source
It works fine in PBI Dekstop. But it fails in service. I can’t seem to figure out what is the issue.
Any help is greatly appreciated!!!
Thanks
Samik Pal
Hi Samik,
If it works in the Desktop but not in the service, I would suggest contact support. There might be an error message that displays somewhere providing a clue, but unfortunately, this is not something that I can help you with.
et
url =
“https://addm-dc.lowes.com/api/v1.1/data/search?format=object&query=SEARCH%20SoftwareInstance%20WHERE%20excluded%20SHOW%20type%20PROCESSWITH%20unique(0)”,
FnGetOnePage =
(url) as record =>
let
Source = Json.Document(Web.Contents(url,[Headers=[Authorization=”Bearer xyz”]])),
data = try Source{0}[results] otherwise null,
next = try Source{0}[next] otherwise null,
res = [Data=data, Next=next]
in
res,
CombinedList=
List.Combine(
List.Generate(
()=>[res = FnGetOnePage(url)],
each [res][Data]null,
each [next_url= [res][Next], res = FnGetOnePage(next_url)],
each [res][Data])),
#”Converted to Table” = Table.FromList(CombinedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Expanded Column1″ = Table.ExpandRecordColumn(#”Converted to Table”, “Column1”, {“type”}, {“Column1.type”})
in
#”Expanded Column1″
In place of token authentication , I replaced key with xyz. I am trying to add Relative path to this Power Query. However, i am not successful so far. Please help me .
Hi Stacia,
I see great discussions all over this post . I am facing a similar issue of refreshing Dynamic data source in Power BI service. Below is how my code looks like :
let
url =
“https://addm-dc.lowes.com/api/v1.1/data/search?format=object&query=SEARCH%20SoftwareInstance%20WHERE%20excluded%20SHOW%20type%20PROCESSWITH%20unique(0)”,
FnGetOnePage =
(url) as record =>
let
Source = Json.Document(Web.Contents(url,[Headers=[Authorization=”Bearer xyz”]])),
data = try Source{0}[results] otherwise null,
next = try Source{0}[next] otherwise null,
res = [Data=data, Next=next]
in
res,
CombinedList=
List.Combine(
List.Generate(
()=>[res = FnGetOnePage(url)],
each [res][Data]null,
each [next_url= [res][Next], res = FnGetOnePage(next_url)],
each [res][Data])),
#”Converted to Table” = Table.FromList(CombinedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#”Expanded Column1″ = Table.ExpandRecordColumn(#”Converted to Table”, “Column1”, {“type”}, {“Column1.type”})
in
#”Expanded Column1″
In place of token authentication , I replaced key with xyz. I am trying to add Relative path to this Power Query. However, i am not successful so far. Please help me .
Hi Stacia,
I’ve been going over your, Chris Web and Gil Raviv (Datachant) work on how to iterate over API pagination when the number of pages are unknown and each call results in data and a random bookmark /cursor to make the next call.
The URL has a a ProjectID in the path and bookmark which are both dynamic.
I managed to pull all the data needed in desktop, but as you can imagine, ran into Dynamic Source issue when refreshing online.
Looking to simplify the issue the project IDs were hardcoded and tried to pull all the bookmarks for each project using Query= with the idea of creating a table with all the URL + Bookmark combinations and run a function that pulls the data for each row.
My issue is that the bookmarks are not updating in the function to make the next pull. See the code below –
let
iterations = 20,
Bookmark1 = “0”,
FnGetOnePage=
() =>
let
Source = Json.Document(Web.Contents(“https://field.dalux.com/service/APIv2/FieldRestService.svc/v2.2/Projects/135353/Approvals?key=****&bookmark=”,[Query= [bookmark=Bookmark1]])),
data = try Source[ApprovalsList] otherwise null,
bookmark = try Source[NextBookmark] otherwise null,
res = [Bookmark = bookmark]
in res,
GeneratedList =
List.Generate(
() => [i = 0, res = FnGetOnePage()],
each [i] each [i = [i] + 1, Bookmark1 = [res][Bookmark], res = FnGetOnePage()], but when it is passed as a parameter PBI Service dynamic source issue happens.
Any idea how to get around this?
Thank you for providing great resource and hopefully in advanced of any help you could provide.
BR,
Ricardo.
I think the bottom part of your code is missing some things, so to get a list of your bookmarks, you need to fix a few things. Here’s the code that I think should point you in the right direction:
let
iterations = 20,
Bookmark1 = “0”,
FnGetOnePage =
(Bookmark1) as record =>
let
Source = Json.Document(Web.Contents(“https://field.dalux.com/service/APIv2/FieldRestService.svc/v2.2/Projects/135353/Approvals?key=xxxxx&bookmark=0″ ,
[Query = [key=”xxxx”, bookmark=Bookmark1]])),
bookmark_test = try Source[NextBookmark] otherwise null,
bookmark = if bookmark_test = Bookmark1 then null else bookmark_test,
res = [Bookmark=bookmark]
in
res,
GeneratedList = null,
List.Generate(
()=>[i=0, res = FnGetOnePage(Bookmark1)],
each [i]
each [i=[i]+1, res = FnGetOnePage([res][Bookmark])],
each [res][Bookmark])
in
GeneratedList
The first issue in your code is that you’re not making it dynamic. In other words, you’re not passing in a parameter that changes each time you call the function. I added the Bookmark1 variable as a parameter to the function.
Then you need to set up the query to use the Bookmark1 parameter. That’s where the Query argument comes in. You need the full URL in the Source variable so that Power BI can validate the query, and then everything that comes after the ? in the URL is placed into the Query argument. The key is hard-coded in this example whereas the bookmark comes from the parameter value. I added in a test to compare the current bookmark to the next bookmark to make sure it actually changes.
In the List.Generate function, you pass in the initial value for bookmark in the first call to the FnGetOnePage function (Bookmark1 = “0”) whereas as you iterate, the value returned by the last call to that function is passed as the parameter for the next call to that function.
Hope that helps!