Filtering date column data in power query

[aatish178]

Hi All,

I want to filter a date column to only show last 3 years of data from today's date in power query. The filtering should be from today's date and it would be dynamic after every day.

Can you please help me to load only last 3 years of data using filtering through power query code

@amitchandak @v-huijiey-msft @gmsamborn @v-kaiyue-msft @v-jialongy-msft 

[ronrsnfld · Accepted solution]

I don't think you can do that in Power Query. I believe you will need to load the entire data set, and then filter.  M-code would be something like:

Table.SelectRows(#"Previous Step", each [date_column_to_filter_on] > Date.AddYears(Date.From(DateTime.FixedLocalNow()),-3))

If you only want to LOAD the past three years, that would need to be done either at the data source, or, depending on the data source, perhaps in your connector (eg if it involves an api perhaps there is an argument to restrict the data).

[lbendlin]

last 3 years of data from today's date

full years or sliding window?  Assuming today is 2024-07-01, what would be the minimum date you wanted to pull? 2021-07-02 ?

[aatish178]

Hi, thanks for the reply... 

I want to load only last 3 years of data from today's date 

So basically it would be like Date>=last 3 years of date from today

[lbendlin]

I want to load only last 3 years of data from today's date 

So basically it would be like Date>=last 3 years of date from today

That's still ambiguous. Including or excluding "today"  from three years back?

[aatish178]

Let's say today is July 3rd then I want to load last 3 years of data from today that is 3rd July 2021 onwards.

If today's date is 5th July 2024 then power bi should load data from 5th July 2021 onwards

If today's date is 10th july, then power bi should load data from 10th july 2021 onwards

The date field has historical data till 1974, I only want to filter it based on last 3 years from today's date.

I want to make it dynamic in power query itself. 

Plz help me if there is any possible solution using power query language code 

[lbendlin]

Let's say today is July 3rd then I want to load last 3 years of data from today that is 3rd July 2021 onwards.

That is not "the last 3 years". That is "the last three years plus one day".  Are you sure you want that?