Help applying Query and Relative path to avoid dynamic data source for Service refresh

[Ricardo_MSS]

Hi All, 

I've a function built to paginate through an API call. It works very well on the Desktop service but when trying to refresh on PBI Service the following error is displayed: 

I've done some research and found a possible solution using Query= and Relative path= HERE but have not been able to apply it to my function. 

Could someone go over my code and help apply this solution, unless there is a better one? 

If you are feeling particularly generous with your time, it would be great to se the condition to terminate the List.Generate loop to exit when the previous bookmark = the current bookmark.  This is probably very simple somethig along the lines of creating a new variable to store the previous bookmark to then compare it with current bookmark, I've just not managed it programatically.

(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"

Thank you in advanced, any help is incredibly appreciated. 

Best regards, 

[Syndicate_Admin]

When fetching API data I can use the scheduled update without paging through the dynamic function.
When I use the dynamic function, I can't schedule anymore.
I've studied crhiss webbs' blog teaching you how to use RelativePath and Query, but none worked.

Has anyone managed the solution to the problem ?

[tawis]

What's the solution to this issue? I am experiencing the same.

[Ricardo_MSS]

Hi thank you for the response @v-janeyg-msft .

Needed to modify your suggestion as it did not include my personal API key. Unfortuntately it returned an nulled table. 

See how code was modified below - 

(ProjectID as text) => 
let iterations = 20, 
Bookmark1 = "0", 
url = "https://field.dalux.com/service/APIv2", 
Repath = "FieldRestService.svc/v2.2/Projects/"&ProjectID&"/Approvals?", 
Q = [key="key=INSERTED PERSONAL KEY&bookmark="&Bookmark1], 

FnGetOnePage= (url) as record => 

let Source = Json.Document(Web.Contents(url,[RelativePath=Repath, Query=Q])), 
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" 

Just for reference - below is a successful API call for the first bookmark:

=Json.Document(Web.Contents("https://field.dalux.com/service/APIv2/FieldRestService.svc/v2.2/Projects/135353//Approvals?key=PERSONAL KEY&bookmark=0"))

Can [Query= ] option to Web.Contents() have two parameters? One for the static API key and the second for the bookmark which would be dynamic?

[v-janeyg-msft]

Hi,  @Ricardo_MSS 

Try to delete '?' in the url.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ricardo_MSS]

Hi @v-janeyg-msft , 

I've removed the '?' from the URL still resulting in an empty table. 

See the updated function and results below: 

(ProjectID as text) => 
    let iterations = 20, 
    Bookmark1 = "0", 
    url = "https://field.dalux.com/service/APIv2", 
    Repath = "FieldRestService.svc/v2.2/Projects/"&ProjectID&"/Approvals", 
    Q = [key="key=API KEY HERE&bookmark="&Bookmark1], 

        FnGetOnePage= (url) as record => 

        let Source = Json.Document(Web.Contents(url,[RelativePath=Repath, Query=Q])), 
        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"

FYI - 

The following works both in excel and in Desktop.

let
    Source = (ProjectID as text) =>
let
    iterations = 20,
    Bookmark1 = "0",
    url = "https://field.dalux.com/service/APIv2/FieldRestService.svc/v2.2/Projects/" &
    ProjectID &
    "/Approvals?key=API KEY HERE&bookmark=",
    FnGetOnePage=
        (url, Bookmark1) as record =>
        let
            Source = Json.Document(Web.Contents(url & Bookmark1)),
            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, Bookmark1)],
            each [i] < iterations and [res][Bookmark] = [res][Bookmark],
            each [i=[i]+1, res = FnGetOnePage(url, [res][Bookmark])],
            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"
in
    Source

As a work around, I've 'cheated' by calling the information needed in an excel table then using it as a source in the PBI report which allows it to refresh online. 

It just creates an extra task of making sure the table is properly refreshed before updating the report in PBI Service. 

I really dislike resorting to these kind of tactics 😞 but the report will work until a proper solution is found. 

[v-janeyg-msft]

Hi, @Ricardo_MSS 

Try this:

Q = [key="API KEY HERE“,bookmark="Bookmark1"]

Best Regards

Janey Guo

[Ricardo_MSS]

Hi @v-janeyg-msft , 

This now work to call the information correctly. Unforetunately Still has an issue with refresh in PBI Service. 

(ProjectID as text) => 
    let iterations = 20, 
    Bookmark1 = "0", 
    url = "https://field.dalux.com/service/APIv2", 
    Repath = "FieldRestService.svc/v2.2/Projects/"&ProjectID&"/Approvals", 
    Q = [key="API KEY", bookmark= Bookmark1], 

        FnGetOnePage= (url) as record => 

        let Source = Json.Document(Web.Contents(url,[RelativePath=Repath, Query=Q])), 
        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"

 Maybe the ProjectID has to be hard coded? 

If that funtion is modified to only pull bookmarks for each project. These could be recombined in a new column.

A function could then be invoked using the full url as parameter. In theory the url / source would not be dynamic.

I'll try that out see how it goes.  

[parry2k]

@Ricardo_MSS did you looked at the link I sent? You are not adding this data source to use gateway but in the gateway admin, you are turning on the setting to use cloud sources refresh thru this gateway. It was very clearly mentioned in my previous reply and I also send the link in that. Please take a moment to read that official documentation.

[Ricardo_MSS]

@parry2k hope I understood this correctly. 

My gateway was already set to Allow users cload data source. See below - 

The oringal report with out the query in question has many sharepoint links as sources as well as databases, excel files, and other SaaS. See below - 

As I mentioned before when the query with this code is included to the same report I get this - 

When I follow the link given by the error (https://aka.ms/dynamic-data-sources) it takes me here. 

I've found some resources to try this RelativePath and Query options but have not been able to figure out how to adapt the function to work with this.  

[v-janeyg-msft]

Hi, @Ricardo_MSS 

It’s my pleasure to answer for you.

According to your description,I think you can modify your query content. The URL parameter of content can't be a variable,you need to add the following things to the second parameter of web.content, then it may not show that it is a dynamic data source and you can refresh in service.

Like this:

(ProjectID as text) =>
let
iterations = 20,
Bookmark1 = "0",
url = "https://field.dalux.com/service/APIv2",
Repath = "FieldRestService.svc/v2.2/Projects/"&ProjectID&"/Approvals",
Q = [key=Bookmark1],
FnGetOnePage=
(url) as record =>
let
Source = Json.Document(Web.Contents(url,[RelativePath=Repath, Query=Q])),
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"

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@Ricardo_MSS exactly, that's why you need a gateway to refresh the online source through gateway as recommend in previous post.

[Ricardo_MSS]

@parry2k My apologies, I missed the link. 

Will check it out and try to apply solution. 

[Ricardo_MSS]

@parry2k I don't even get the option to set up the gateway when the query is in the report. 

First image includes query in question, second image the query is removed. 

[lbendlin]

The best option would be if your initial API call would return the number of pages (bookmarks). That would allow you to generate a list of URLs, call for each URL's content, and then concatenate them, all in one go.

If you don't have that  I would use a recursive function that keeps calling itself (and accumulating the returned data) until the bookmark stops changing.

Recursive Functions in Power BI / Power Query — Powered Solutions

[Ricardo_MSS]

Hi Ibendlin. 

I cannot go with the first option as the number of bookmarks are unknown before hand. 

The section of the code below, runs the function to make an API call, update the bookmark and then make another call untill the number of iterations reaches the set limit. I still need to figure out how to set it to stop when bookmarks don't change. Its there just badly coded.

GeneratedList =
List.Generate(
() => [i=0, res = FnGetOnePage(url)],
each [i] < iterations,
each [i=[i]+1, Bookmark1 = [res][Bookmark], res = FnGetOnePage(url)],
each [res][Data]
),

[parry2k]

@Ricardo_MSS if this works in desktop it means there is no issue with the code. I think you need to have gateway to refresh even though your data source is in cloud. In gateway settings, turn on  "Allow user's cloud data sources to refresh through this gateway cluster. These cloud data sources do not need to be configured under this gateway cluster.Learn more

[Ricardo_MSS]

Hi Parry thank you for respoding. 

As you say the code runs well in Desktop and I am able to refresh online. 

Regarding the Gateway that might not be the issue. This query was recently added to an existing report that worked well both online and in desktop. The report stopped refreshing online when this query was added. 

When removed the report refreshed again online.