Forum Discussion

Ricardo_MSS's avatar
Ricardo_MSS
Icon for Helper II rankHelper II
5 years ago

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

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:      ...
Need Help
parry2k's avatar
parry2k
Icon for Super User rankSuper User
5 years ago

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's avatar
    Ricardo_MSS
    Icon for Helper II rankHelper II
    5 years ago

    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's avatar
      v-janeyg-msft
      Icon for Community Support rankCommunity Support
      5 years ago

      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.