Cannot filter on date

[Anonymous]

Hello all,

I have two huge CSV files that I have appended, but I really need to thin out the rows, as there are literally millions, So I am trying to filter anything older than 6 months, but if I try to put a filter in the query editor it sayd it cannot parse as a dateTime.

The original CSV has the date format 15/09/2017 09:52:50, and when I changed the type to Date Time, it shifted from the left to the right of the data cell, So I would expect that it has accepted the data as DateTime, but when I them try to filter the date to be anything after the 31/08/2019, it gives me the error that it cannot parse the data as DateTime.  I am not writing any manual queries for this, I am looking at the table in the Power Query editor, clicking the down arrow to the right of the column header and filtering on after, and selecting the date from the calendar.

Am I doing something stupid, or is this process just going wrong?

[Anonymous · Accepted solution]

I have figured out that the date error is itself an error, as if I change the filter to anything BEFORE 31/08/2019, it works, it just doesn't seem happy tying to filter dates after 01/09/2019.  Now I don't know if there is a limit to how many lines it can see in a CSV or if the fact that the CSV is always open and being written to all the time throws it.

[amitchandak]

Power BI setup is required to support DD/MM/YYYY kind of date. If you have done that

Then have date column to join with date table

Date = Table[Datetime].date

If you date calendar is in mm/dd/yyyy format

then

Date = Date(right([datetime],4),mid([datetime],4,2),left([datetime],2))

[Greg_Deckler]

Are you sure that you are on the last row of your query when you are trying to implement your filter? If you are on a row higher up before you converted to date time then that might be the cause. Only thing I can think of.

[Anonymous]

Hi, Thanks for your response.

Yes, it is the last action in the query.  I cannot use the DAX, as I want to get this done to the two seperate data sources befor they append together, so I need this to happen in the Query.