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.
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?
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.
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?
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.
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:
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:
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?
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.
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.
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.
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.