Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Date column Error Power Query Editor

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

 

 

6 REPLIES 6
amitchandak
Super User
Super User

@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
Not applicable

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"))

;

Capture.PNG

Thankful for any further help! 

;

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

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

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


mahoneypat
Employee
Employee

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

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.