Scheduled refresh breaks in Power BI web service with dynamic url

[Ivana]

• I'm facing a refresh issue in Power BI web service when using a dynamic url to get the data. Here is a detailed explanation.
  

  I'm getting data either through Web or OData Feed via the following query:
  

  https://dummy.com/API/Dsl/v1/Attributes(ElementId=**********,%20Name='TimeSeriesValue')/RetrieveValuesTimeSliced(Start=2018-10-19T18:30:00Z,End=2018-10-25T00:00:00Z,TimeSpan='PT10M')

  in which element ID (*****), start date (2018-10-19T18:30:00Z) and end date (2018-10-25T00:00:00Z) are dynamic. End date is the current time, start date is current time minus 7 days and I got ca. 200 different IDs that I import from the excel file into one column in Power BI.
  
  I solved this by creating 3 parameters (GUID, StartTime and EndTime) and a following function:
  
  let
         Source = (GUID as text, StartTime as text, EndTime as text) => let

                 Source = OData.Feed("https://dummy.com/API/Dsl/v1/Attributes(ElementId=" & GUID & ", 20Name='TimeSeriesValue')/RetrieveValuesTimeSliced(Start=" & StartTime & ",End=" & EndTime & ",TimeSpan='PT10M')")

         in
                 Source
  in
         Source
  
  Finally, I invoke this custom function to a table containing actual IDs, start and end times, and get my data imported. This works like a charm in Power BI Desktop, no issues. Also refresh works in Power BI Desktop, no problem.
  
  However, when I publish to Power BI web service, refresh doesn't work anymore. I read many posts online, but wasn't able to find a solution for my problem.

  For example, this post: https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power.... Url is this example is https://data.gov.uk/api/3/action/package_search?q= which contains "?" that can be exchanged by "Query", but I'm not sure if and how could I do this for my example.

  Appreciate any help!

  Ivana

[v-yuezhe-msft]

@Ivana,

I don't think that schedule refresh would work in Power BI Service even if you involve query parameter in the OData.Feed function in the Power Query code . You can check gaccardo's reply about how to add query parameter in OData.Feed() function in this similar thread. OData.FeeD function works in a similar way as VSTS.Feed function.

As confirmed by PG, OData filter is not supported in Power BI Service, you would need to filter from the Power Query editor that generate a call to the function Table.SelectRows. Thus, it is not possible to schedule refresh in this case.

Regards,
Lydia

[Ivana]

@v-yuezhe-msft

Thanks for your reply!

Unfortunately I can't import all data and then filter in Power Query editor with Table.SelectRows cause there would be billions of data points to import. I'm importing time series with 1 minute interval that go way back in the past.

Instead of OData.Feed(), I could also use Web.Contents() to get the data, however still get the same error.

I read this blog (https://blog.crossjoin.co.uk/2016/08/23/web-contents-m-functions-and-dataset-refresh-errors-in-power...) that explains how to do this using Relative Path and Query, but I'm struggling to make it work for my example.

[Ivana]

My query with Web.Contents() is:

Source = Json.Document(Web.Contents("https://dummy.com/API/Dsl/v1/Attributes(ElementId=" & GUID & ", 20Name='TimeSeriesValue')/RetrieveValuesTimeSliced(Start=" & StartTime & ",End=" & EndTime & ",TimeSpan='PT10M')"))

Here's what I saw on Chris's blog.

1. Like this scheduled refresh in Power BI service does NOT work (similar to my query):

Source =Json.Document(Web.Contents("https://data.gov.uk/api/3/action/package_search?q=" & Term)),

2. However, it works when changed in the following manner:

Source =Json.Document(Web.Contents("https://data.gov.uk/api/3/action/package_search",[Query=[q=Term]])),

I'm struggling to update my Web.Contents() function with "Query" (similar to above 2.) as my url contains no "?". Any ideas?

[v-yuezhe-msft]

@Ivana,

You can use RelativePath and Query parameters in Web.Contents() function as discussed in the following similar thread.

https://community.powerbi.com/t5/Integrations-with-Files-and/Data-source-accessing-a-web-URL-which-r...

Regards,
Lydia

[Ivana]

@v-yuezhe-msft

Thanks for your reply. I looked into it and try to apply it on my case, but unfortunately, I can't make it work. Any idea where I'm making a mistake?

My starting query is:

Source = OData.Feed(“https://dummy.com/API/Dsl/v1/Attributes(ElementId=”
& GUID & “,%20Name=’TimeSeriesValue’)/RetrieveValuesTimeSliced(Start=”
& StartTime &“,End=”& EndTime &“,TimeSpan=’PT10M’)”)

Here's how I tried to convert it (option 1):

Source = OData.Feed(“https://dummy.com/API/Dsl/v1/Attributes(ElementId=”, [Query = [GUID], RelativePath = “,%20Name=’TimeSeriesValue’)/RetrieveValuesTimeSliced(Start=”, Query = [StartTime], RelativePath=”,End=”, Query = [EndTime], RelativePath=”,TimeSpan=’PT10M’)”])

This is the option 2:

Source = OData.Feed(“https://dummy.com/API/Dsl/v1/Attributes(ElementId=”, [Query = [GUID,StartTime,EndTime], RelativePath = [“,%20Name=’TimeSeriesValue’)/RetrieveValuesTimeSliced(Start=”,”,End=”,”,TimeSpan=’PT10M’)”]])

Really appreaciate your help!