Forum Discussion

Ivana's avatar
Ivana
Regular Visitor
8 years ago

Scheduled refresh breaks in Power BI web service with dynamic url

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 vi...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
Ivana
Regular Visitor
8 years ago

Anonymous

 

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-bi/) that explains how to do this using Relative Path and Query, but I'm struggling to make it work for my example.

 

  • Ivana's avatar
    Ivana
    Regular Visitor
    8 years ago

    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?

      • Ivana's avatar
        Ivana
        Regular Visitor
        8 years ago

        Anonymous

         

        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!