Incremental Refresh based on loaded date or a custom column

[Anonymous]

Hi,

I would like to set up incremental refresh based on "loaded date".

My data set does not have a date in it, so, I add a new column, and fill it using M in the query editor

Loaded = DateTime.LocalNow()

In a month, I will get a new file that I want to add to the dataset. (I do not want to overwrite the existing dataset)

This will happen every month - so the dataset will keep getting larger. I want to eventually store many months of data.

So, I have set up incremental refresh using "loaded date", but it does not seem to be working.

Does anyone have a solution that allows me to keep the original data or how to set up incremental refresh using my "loaded date"

Thanks in advance

[RN1]

I had a very similar problem to this, no dates and no history on the actual files (all out of my control). One problem I ran into with the incremental refresh based on a 'Loaded = DateTime.LocalNow()' is that if you ever need to change the dataset for any reason your local now date is going to update and you're going to lose all of the history that was built up via the incremental refresh.

My solution was to instead get data from a folder location (where all of your files are stored - either saved and renamed manually or via some FTP/Macro/VBA/powershell script instead) and have the file names as the relevant dates.

Power BI: How to Combine Multiple Excel Files from a Folder - YouTube

I've not tested this yet but I think you should still be able to have this set as an incremental refresh on your dates from the file names and you still have the files to reference if your incremental ever fails.

[v-lili6-msft]

hi, @Anonymous 

For incremental refresh, datasets are filtered by using Power Query date/time parameters with the reserved, case-sensitive names RangeStart and RangeEnd. 

Since your data set does not have a date in it, It difficult to filter your dataset.

and In a month, you will get a new file that I want to add to the dataset. So i recommend you use get data from the folder,

Then use file attributes(e.g. date Modified/ Date created ) as the date filter.

Here is a post for you as reference:

https://community.powerbi.com/t5/Desktop/Updating-Excel-Data-Sources-Best-Practices/m-p/600762#M285816

Best Regards,

Lin

[Anonymous]

Thank you.

I Tried doing that, but the incremental refresh still did not work.

I was able to bring in the file attributes - date created, and then set up an incremental refresh on that column.

I loaded the data into my model, and published it.

To test it, I chnaged the data in an old file (that has a created date outside the incremental refresh settings), and then I refreshed the dataset.

The old data still refreshed.

Am I missing something or doing something wrong?

[v-lili6-msft]

hi, @Anonymous 

Do you have all the right Settings for it? you may refer to these articles

https://docs.microsoft.com/en-us/power-bi/service-premium-incremental-refresh

https://powerbi.microsoft.com/en-us/blog/incremental-refresh-in-power-bi-premium/

 https://community.powerbi.com/t5/Desktop/unable-to-setup-incremental-refresh/td-p/511554

Best Regards,

Lin