Incremental Refresh

[DemandMGR]

I am trying to utilize incremental refresh to cut down on my refresh time. I have a Sales Table that becasue of the amount of data is an amended table. I have about 30 tables Amended into one table, let's call it Historical Sales. This ameneded table is massive going back to 2019 and to cut down on time I decided to look into oncremental refresh.

I am trying to fold in all files that are not being updated with new data, only one of the 30 files is getting data added to it daily, we will call it Decemeber 2021 sales. Do i set up the incremental refresh on The Historical Sales amended file or do I set it up on the December 2021 file?

I have tried setting it up on the Historical Sales file, I set the RangeStart after or equal to 12/1/2019, which is the start of the sales data and the RandgeEnd to before or equal to 12/7/2021. Then i set up the filter on the Historical Sales table. When i go to click on Incremental Refresh in the field options it tells me I need to set up Incremental Refresh still. 

I cant seem to figure out what I am doing wrong. Should I set the filter to fold the data from every file but the one that is being updated daily or should I set the filer to fold all the current data? 

[rsbin]

@DemandMGR, @jeroendekk ,

Allow me to kick in a couple of thoughts here that may simplify things.

In Power Query, you have the ability to uncheck "Include in Report Refresh".  Right click on your "Historical Sales" table and you will find this option.

The only table that needs to be refreshed is your "December 2021" file.  I would give it a name such as Current File.  Since it is only a month's worth of records, it shouldn't be too onerous to load.

Then, in Power Query merge Historical with Current.  This new table is now "All Sales" or something to that effect.

Again, just wanted to throw out another option - hopefully not confusing the issue.

Best Regards,

[DemandMGR]

Yes. I have done that for when I am refreshing from the desktop but ultimately I want to set it to auto-refresh and from what I have read unchecking "Include in Report Refresh" only works for the desktop version.

Unless I am wrong about that?

[rsbin]

@DemandMGR,

I believe you are right with respect to the Incremental Refresh.

The way I handled the Date/Time issue in Power Query was to "Duplicate" my Date Column, format it as Date/Time (this will add the time 12:00:00).  I called this "DateForIncr.Ref" and applied the Parameter filters to this column.  This will leave your original Date column intact for your Visuals.

Hoping this tip helps.

[DemandMGR]

I will try that for now. Thank you! Although after changing it to Date/Time format I am running into a new error stating "Unable to confirm if the M query can be folded. It is not recommended to use incremental refresh with non-foldable queries" 

[rsbin]

@DemandMGR,

I think it is more a Warning message rather than an error.  I too get that message as well and I am using SQL tables.  I don't think you will have an issue with Excel files.

I have tried studying foldable queries, but haven't been able to gain an understanding as yet.

Hope it all works out for you.

[DemandMGR]

I have one more question. Just to make sure I understand what the parameter is doing. The data that is between the RangeStart & RangeEnd is going to get locked into Power BI Service? Then it will only refresh the new data that is added to the files during the refresh? Or do I need to extend the RangeEnd for it refresh the new data that is being added?

[rsbin · Accepted solution]

@DemandMGR,

From what I understand, you only need to set your parameters once. Upon refresh it will look at the period settings you entered when creating your Incremental Refresh. 

I usually set my RangeEnd a year or two in advance (ie. 1/1/2023).  I think I will need to adjust these at that time. 

But at this point, there are articles and youtube videos that will probably provide a much more clearer explanation than I could.  Two examples:

https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview

https://www.sqlshack.com/an-overview-of-power-bi-incremental-refresh/

[DemandMGR]

You are correct it was a warning. It let me turn it on. Wow! I can't believe I didnt see that. 

[jeroendekk]

Hi @DemandMGR 
When you madde the parameters RangeStart & RangeEnd did you make them a DateTime datatype. Thats the only way it will work.

By the way what is your source, what type of files? Is it a source that supports incremental refresh?

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Best regards,
Jeroen

[DemandMGR]

I did not realize it only worked for Date/Time, I have it set for Date because all the sales files are in Date format. So if I change it to Date/TIme I would also have to change all the source files Date column to Date/Time as well for it to work?

The source files are excel files. Someone enters the sales data from the previous day in the most recent excel sales file and then I am refreshing to add the new data. What I am hoping his will do is only refresh the file that is being updated daily. 

[DemandMGR]

I am not getting an error stating that "Unable to confirm if the M query can be folded. It is not recommended to use incremental refresh with non-foldable queries"

Do you happen to know what is causing this?

[jeroendekk]

Hi @DemandMGR 
Sorry, Excel files are unstructured data and do not support query folding. They also do not support Automatic Incremental Refresh with the parameters. Datasource that use SQL and Odata do. 

So the only solution is to manually create a incremental logic bij unchecking the "include in refresh" option for query's that do not need to be refreshed.

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

Best regards,
Jeroen