Incremental refresh with no parameters advice

[tbobolz]

Hi All,

I'm new to setting up incremental refreshes and I have read through microsoft's and other's instruction for setting these up. However in my case I have daily data being dropped to my gateway folder that over writes the previous days data. So in my refresh, I just need it to grab the entire data set and add it to my report data. I can not find a way to set it up with out appling a parameter. Am I missing something or would you have any advice or links to instruction I'm unable to find that might help?

Thanks as always for any help you can offer.

Terry

[Yaa_D]

Hey @tbobolz , sorry for barging in, 
I have a very similar requirement - a 'point in time' data that I need to keep adding rather than overwriting upon refresh (in order to create trends).
I couldn't understand if you actually managed to do this using the Incremental Refresh feature.
If you did, I'd be happy if you could elaborate a bit on your solution.

Thanks a lot!

[tbobolz]

Hi Yaa_D

No this was ot achievable on a csv file, and I moved to doing a simple refresh of the entire data set each day, as it is not that large. Also, I can control my data set through the software I am exporting it from. Sorry, probably not the answer you were looking for. 

I do need to try this again on another tool I am developing, but as suggested in this conversation, it does not work with csv files. I'll need to use an SQL export and have IT help me do the initial set-up.

Good luck on your project!

[GilbertQ]

Hi there

If you are always getting the entire dataset, then why would you need incremental refresh?

Unless you mean that you want to load the new data and keep the existing data in your model.

If so you would need to use the RangeStart and RangeEnd columns. And as far as I know I do not think Incremental refresh is supported for local files such as CSV or Excel file.

[tbobolz]

Thanks Gilbert! 

Yes I do want each new days data to be added be my existing data in my model. I'll try yuor suggestion, since I have no defined range, is there a wildcard indicator I can use to grab all?

Terry

[GilbertQ]

Hi there

Currently you would have to use the RangeStart and RangeEnd which have to be DateTime columns.

[tbobolz]

Hi Gilbert,

Sorry for the delay, I'm finally back on this project. Maybe I am not fully understanding this process. I've tried to create my incremental refresh and have read through most of the set-up instructions. I'm able to define paramater as you suggested based on a datetime column. When I define the incremental refresh I get a waring about M query folding (As I'll be grabbing daily csv file overwritten daily from a server file on a gateway connection), but I it does allow me to proceed. It seem this set-up is for refreshing past data as it has me setting-up past year paramaters from the paramater column. Is this still what I need to do.  I choose to look back 6 years and it seemed to work fine; yet, I'm concerned about the "folding" warning

Ultimately what I want to do a one time load of the past year's data, then I'd like it to load the new data for each new day starting tomorrow and continue loading the previous days new data from then on out.

This is my first time with this type of set-up, thank you for your help and patience with me.   

[tbobolz]

Sorry, I actually set my "store rows" and "refresh rows" to 1 day.

[GilbertQ]

Hi there

The reason for the warning is that typically when setting up incremental refreshing it is done where it can query the data source to only bring back the data it needs.

When using a CSV file there is a high possibility that it will have to read all the CSV files to find the files that need to be updated. And by reading all those files it would be quicker to just refresh them all.

By nature Power BI cannot do query folding on CSV files.

[tbobolz]

Since I do not need it to read the past rows to look for a changes, I just want it to load the new daily data, should I be using a different refresh method? 

Thanks

[GilbertQ · Accepted solution]

Hi there

Even though you set it to only go back one day, because it cannot do query folding it has to read all the data to find the last day.

Incremental refreshing is the right approach but it only works on data sources that can do query folding which is typically database systems

[tbobolz]

Thank you for your time!

[GilbertQ]

No worries, always happy to help!