SQL Import Limit Data based on Dates

[Anonymous]

Hello,

Importing data from an Sql database which contains nearly 5 years of data, but I am only interested in the past 6 months of data. at the most.

Is there a way to limit the data when the Import / refresh occurs, I think there are that many records and relationships to other tables it never seems to get off the Evaluating Data.... so have never got into the Transform Data to apply any filters.

I have a DATE field in a DATE table that was hoping to link as there is a relationship to the other tables.

TIA

Andy

[Anonymous]

@Anonymous 

You can use incremental refresh, which is most suitable for structured relational data sources, such as SQL databases, it extends scheduled refresh operations by providing automated partition creation and management for dataset tables that frequently load new and updated data, You can customize the time range to refresh the data instead of importing it in full every time.

However, it should be noted that the incremental refresh will load all the data for the first time. The loading time will be based on how much data you have. After that, the data will not be imported in full, and the data will be loaded slowly.

1. Create two date/time parameters, RangeStart and RangeEnd.

2. Configure incremental refresh on the desktop report page.

For the specific operation of incremental refresh, you can check the following link:
https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-configure

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

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

Best Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , One way in connection advance property is you can write a SQL and handle the logic of the last 6 months there.

Secondly do not load data, directly press transform data, and there you can use parameters to control, right-click on column filter, and use a custom filter

refer this this part https://docs.microsoft.com/en-us/power-bi/connect-data/incremental-refresh-overview#create-parameters