Date column Error Power Query Editor

[Anonymous]

Hi,

I have a CSV-file with a date column that contains some values that are not recognized as date, and hence show as Error in the Power Query Editor. I have chosen to remove Errors but still end up with one row with stating "Error" in the all columns.

This results in that I can´t load the table when I filter on the date column in the Power Query Editor. When trying to load the query to a table in Excel I get the following error message: "DataFormat.Error: We couldn't parse the input provided as a DateTime value." 

Does anyone know how to fix this? 

/Helena

[amitchandak]

@Anonymous , Check column (datetime) column where are you getting that.

Change the date time column text and check does it remove the error , Try find out one that does not have correct format

refer how to handle error in power query
https://www.youtube.com/watch?v=OE6DPmKqN7s
https://www.youtube.com/watch?v=9-Lag0VOiTs

debug issue
https://www.youtube.com/watch?v=OE6DPmKqN7s

[Anonymous]

Hi @amitchandak 

I changed to text, however the error at the bottom is still left. It still looks like this, and I suspect this error unables me to filter on the date column and then load to table in Excel (?)

the code is the following when having removed errors and changed to text, and still getting the last row of Error.

;#"Changed Type1" = Table.TransformColumnTypes(#"Removed Errors",{{"DeliveryDate", type text}}),
; #"Filtered Rows2" = Table.SelectRows(#"Changed Type1", each ([DeliveryDate] = "11/10/2020 9:07:31 AM"))

;

Thankful for any further help! 

;

[mahoneypat]

Since you have an error across the whole row, something other than your date type error is going on.  You will need to filter (or change) that row before the steps when it changes to all errors.  Is there a step w/o errors?  Which steps causes them?

Regards,

Pat

[Anonymous]

Thanks for reply! Is there a way to identify which row number this has in the original CSV-file? I can only find it in Power Query Editor when I filter like above and the error row is not row number 2 in the CSV-file for sure

[mahoneypat]

I would keep all your columns as text and load that table, so you look at all the data in the Data View (pull down each column header and see all the values in each column).  I'm guessing one will stand out, and that the one to filter out before you do your change type steps.  Or you could look at the starting csv file in Excel to do same.

Pat

[mahoneypat]

Please share some examples of the text values prior to changing to datetime, so a specific solution can be suggested.

Pat