Date Type format restrictions

[Anonymous]

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.

[Anonymous · Accepted solution]

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.  

[amitchandak · Accepted solution]

@Anonymous , UK date format setup

https://community.powerbi.com/t5/Desktop/How-to-apply-UK-date-format-dd-mm-yyyy-in-Date-slicer/td-p/599712

[Anonymous]

What error are you getting exactly? maybe you could share a screenshot or some sample data? PBI should read that data format just fine.

[Anonymous]

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.

[Anonymous · Accepted solution]

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.