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

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.

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.