Forum Discussion
Date Type format restrictions
Hi all,
I'm using an excel file as a data source and the file has a Date column with a certain date type (for example dd.mm.yyyy). Sometimes I received the updated file with rows with different date type (dd-mm-yyyy) and PBI cannot accept it. I can replace the symbol ("-" to ".") from excel or to use the Replace value option in the query, but is there a way to set something like a condition and all symbols different from "." to be replaced with "-" or the incorrect date type automatically to be transformed to the correct one?
Thank you.
You can set the query editor to do so.
go to Transform
then on Query editor, you want to select the step "changed type" on the right-hand side:
Then change the data type on your date column to text ABC (you'll be asked if you want to insert a step, just say yet each time)
Now with the date column selected, go to find and replace (from the home tab).
Value to find = ;
replace with = .
Repeat the above sept for each character you want to replace, then change the data type back to date and it should work fine.
Anonymous , UK date format setup
4 Replies
Anonymous , UK date format setup
Hi Anonymous ,
The error messages are: Expression.Error: We cannot convert the value #date(2020, 5, 22) to type Text.
or: "We cannot parse the date"
If I replace the wrong symbol with the right one in excel-everything works fine, but my main ask is whether there is an option to set a rule with which the received wrong date to be changed to the correct one. (the wrong date may includes "/"; ","; "-")
Thanks.You can set the query editor to do so.
go to Transform
then on Query editor, you want to select the step "changed type" on the right-hand side:
Then change the data type on your date column to text ABC (you'll be asked if you want to insert a step, just say yet each time)
Now with the date column selected, go to find and replace (from the home tab).
Value to find = ;
replace with = .
Repeat the above sept for each character you want to replace, then change the data type back to date and it should work fine.