cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Re: Incremental Refresh Error

This is a little frustrating. I've set up the parameters properly in Power BI Desktop, added a filter to a date/time field with the parameters, set up the table with incremental refresh, and published it into a premium workspace. After all of that, the paramters simply don't show up under the "parameters" section in the Power BI Service.

 

Sample.png

 

This then leads to the same error that the original poster is referring to.  Does anyone know why aren't the parameters showing up properly in Power BI Service?

Highlighted
Helper III
Helper III

Re: Incremental Refresh Error

The parameters don't show up by design for now. This is a known and documented limitation.

 

My guess for the why is the handling of the RangeStart and RangeEnd parameters, which has to be automatic for the incremental refresh. Hence they had to disable their management on the UI, because that would lead to confusion. And instead of implementing it in a granular way - which may be underway for the generally available version -, they simply disabled parameters in general.

 

I really dislike this constraint, because from the end user's point of view it makes no sense, and it imposes a big limit on how one would deal with report development.

Highlighted
Helper II
Helper II

Re: Incremental Refresh Error

So if the parameters are not showing up in the Power BI Service and refreshing the dataset causes an error due to the parameters not being there, does this mean that incremental refresh isn't going to work currently?  Is there a work around to this or are we stuck waiting for a more stable release by Microsoft before we can use incremental refresh?

Highlighted
Helper III
Helper III

Re: Incremental Refresh Error

The parameters have the values, which you were set in Power BI Desktop before you published the report to the Power BI Service. Certainly the refresh still works, otherwise releasing this feature wouldn't have made any sense at all.

Highlighted
Helper II
Helper II

Re: Incremental Refresh Error

I guess I don't understand then.  I've set up the RangeStart (Value of 01/01/2018) and RangeEnd (Value of 12/31/2019) parameters in Power BI Desktop.  I've implemented them in a table with a modified date >= RangeStart and modified date < RangeEnd. 

 

I've then went to the incremental refresh properties of this table and set the following rules:

Sample 3.PNG

 

I went to the source data and saw that "Modified" date for all rows is 6/21/2018 5:26 PM.  I published this to a premium Power BI workspace.  I then added a new row to the source data where the "Modified" date now shows 6/26/2018 12:40 PM.  

 

With all this set, I expect Power BI to keep all of the data when I hit refresh since the data is within the 10 days "store rows" rule I defined in Power BI Desktop.  I then expect Power BI to pick up the one additional row of 6/26/2018 since it is within the 2 days "refresh rows" rule I defined in Power BI Desktop and add it to my dataset.

 

Instead, when I hit refresh in Power BI Service, I get the following error:

Sample 2.PNG

 

I thought this error shows up because Power BI Online doesn't seem to show my parameters, but you're mentioning that that is by design for Microsoft.  So then, why am I getting this error which seems like it's saying that Power BI Service isn't detecting any parameters in my report?

Highlighted
Helper III
Helper III

Re: Incremental Refresh Error

Now is the time to dig in to the details of implementing incremental refresh. The details, which are not documented yet.

 

I ran into the below or a very similar error when I had composite queries combined with incremental refresh. E.g. you have a query, which uses another query to produce its result - one way or another. This may be a classical query append or merge. Or it may be just an innocent looking helper query for the query string template. This is typical in scenarios, where you use the various parameters to put together the SQL command dynamically.

The mashup engine checks for each query involved, and attempts to pass the range parameters to them. If the query takes them and does something with it, then there is no error. If the parameters are not used at all, then an error like yours is thrown.

PBI Desktop detects this requirement only on the final query: if the range parameters are not used, then it doesn't allow incremental refresh on that query at all.

 

Here is the catch:

The final query may have the range parameters used, but a subquery may not use it. This case remains unchecked in PBI Desktop, and it will allow incremental refresh to be configured. But later on it will throw an error in the Power BI Service at the first data refresh attempt.

 

So if this is your case, then make sure that you use the range start/end parameters in all the referenced queries. They don't necessarily need to be meaningful, but they must be used even if they don't do anything. Or eliminate the subqueries if you can.

 

This was only one theory for the error based on my experience. There may be another explanation too.

Highlighted
Helper II
Helper II

Re: Incremental Refresh Error

You were absolutely correct in your analysis of me having another query that was building my 2nd query which is where the parameters were filtering a "Modified" date.  

 

I removed this query for testing purposes and put all the logic in one query with the parameters at the end filtering my "Modified" date.  Unfortunately, I still get the same error in Power BI Service.  All I've left in my data model is one query and the two parameters to test.  I still get the same error.  I'm at a loss of solutions now.

Highlighted
Frequent Visitor

Re: Incremental Refresh Error

Just adding my name to the list. I can only cross my fingers that it's been fixed in the upcoming update. I am getting the same error, and although I am using merged queries I have followed the instructions provided in the documentation.

 

It would be good if the desktop was also able to utilise the incremental refresh and therefore throw up the error before publishing to the service. 

Highlighted
Helper III
Helper III

Re: Incremental Refresh Error

You can always open a support case to let them dig out the details of the generic error, which you see. Digging in the dark left me out of clues for moving on in your case.

Highlighted
Helper III
Helper III

Re: Incremental Refresh Error

@hoffmanrat

I agree that having the incremental refresh supported in the Power BI Desktop application would help a great deal. I would be surprised if it wouldn't be in their roadmap. In the meantime, maybe a new idea should be opened for that - if there isn't one already. If anybody finds it or creates it, please link it here.

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors