Filter a date by current date in PowerQuery / PowerBI

0
votes

I'm creating a report in PowerBI, and need to filter out some erroneous record from my source. Its a payment table, and some records are with a future date, eg in 2799. I'd like to make a Filter to remove records after today + 1 year. I already had this filter :

= Table.SelectRows(_cobranca, each [Vencimento] >= DATA_LIMITE)

DATA_LIMITE is a parameter, and the code above is already working. I tried to change it to :

= Table.SelectRows(_cobranca, each [Vencimento] >= DATA_LIMITE and [Vencimento] <= DateTime.LocalNow())

But I'm getting this error:

DataFormat.Error: Syntax error in date in query expression '[_].[Vencimento] >= #2020-01-01 00:00:00# and [_].[Vencimento] <= #2020-10-21 10:58:07.4411693'.

It seems that DateTime.LocaNow function is not returning the date in the correct format.

1
powerbipowerquery
Do you get the same error if you put parentheses around each inequality? I think it might be trying to and a couple of dates. – Alexis Olson
@AlexisOlson, I did what you suggest, but it didn't help. It showed the same error message. It seems it removes the parenthesis when it compiles the expression. – Marlon
Nah, it's probably something different then. Are they all DateTime format or are the [Vencimento] column and DATA_LIMITE just Date type? – Alexis Olson

1 Answers

0
votes

Replace DateTime.LocalNow() with Date.From(DateTime.LocalNow()) as I assume your [Vencimento] column (due date?) is just a date data type not date time