M Query Where date field is 45 days older or greater

[rjs2]

I have been struggling with this for awhile, I am writting a report looking for stale checks.  I dont want to pull all the checks in an issued state, only ones that are 45 days or older.

What is the correct syntax, this isnt working:

""TABLE"".""DATE"" <= Date.AddDays(DateTime.From(DateTime.LocalNow(),-45))

Thanks,

RJ

[amitchandak]

@rjs2 , You can create a column in M like

if [Date] < Date.AddDays(DateTime.Date(DateTime.LocalNow()),-45) then 1 else 0

[rjs2]

I want it in M Query as part of the data pull.  I am loading data, and I do not want to load data that are 45 days or sooner.  I am trying to keep data volume in my pull as low as possible.

[v-kelly-msft]

Hi  @rjs2 ,

Using below M codes:

"Filtered Rows"== Table.SelectRows(#"Changed Type", each [Date] < =Date.AddDays(DateTime.From(DateTime.LocalNow()),-45))

The complete M codes are as below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RdTBbQMxDETRXnw2kCUpSutaDPffRhKYf+b6T4/CQO/3I37iJ6+Mx/MRj8/zPyQhJxShJizCmtCEnrAJe8IhnAk34Z7wIrwmxCXZRTIWbYgbeEPgQBwiB+YQOlCH2IE7BA/kIXpgD+EDfUqf6FP61Fv7sdGn9Ik+pU/0KX2iT+kTfUqf6FP6RJ/SJ/qSvtCX9PXVp6dTOUXnVE3xeNYUHVM9RbfUnqJT6kzRJXVP0SH1mqI71oVQdyyhpV6ovaEF2xtauL2hBdwbWsi9oQXdG1rYvaEF3htq9N5Qo/eGWm8ufaP3hhq9N9TovaFG7w01em+o0XtD/dWXp7CvKVLtmCLUziky7Zoi0l5T/JX0FIH2niLPPlP0mPtGqMfcQkt9UHsKB7ancHB7Cge4p3CQewoHuqdwsHsKB7yncNB7Cge9p3D/6T+/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type datetime}, {"Value", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] <= Date.AddDays(DateTime.From(DateTime.LocalNow()),-45))
in
    #"Filtered Rows"

Then you will only load the value which are 45 days or older.

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[rjs2]

But doesnt that still pull any record that is less than 45 days old?

I will repeat.  I DO NOT want to pull a record that less than 45 days.  I only want to pull (select) the records that are 45 days or greater.  I dont want that many records going thru the pipe and then being filtered on.  I am pulling from a production database and I am trying to limit how much data I am pulling.  I want to add it to the "WHERE" part of the query, not after the data is pulled and loaded.

Also, this is causing refresh to fail thru personal gateway.  I want to keep the dataset as small as possible with the smallest data pull i can do.

Am I missing something?  All the suggestions are to filter, after the data is pulled.