import new folder data how to deal with date issue ?

[Anonymous]

Hi All

I start to add more new data to PBI fle , when i add the new CSV file , the date field is in text format , when i try to convert the date field to date , i get error mg , what else i can do ?

[mussaenda · Accepted solution]

Hi @Anonymous,

it is showing error because the date format that power bi detecting is dd/mm/yyyy.

Try to change the Regional Setting of the report. Options and Settings->Options->Regional Setting

if it is English UK , format is dd/mm/yyyy

if you select Englisg US , format is mm/dd/yyyy

Hope this helps.

[PhilipTreacy]

Hi @Anonymous 

It's not so much an error as a warning.  Looks like you have dates stored as text.

When you bring the data into PBI with PQ you should change the data type in the PQ Editor.  My guess is you've left the date column as text.

In PBI you can click on the Data icon (Left panel), select the 'Dates' column and then change the type from Text to Date - which I think is what you are doing to get this warning appear?

Changing the text dates to actual dates is fine.

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[amitchandak]

@Anonymous , Warning is OK. As long as the new data type works.

To me, it seem like mixed date fomat issue. UK/US or some with / some with - etc

First, do some tranformation to correct it. Share some sample.

[Anonymous]

Hi Phil

Thank you phil , i try to use PQ to change :-

When i convert to date field from text to date , i see many error , as after convert date field missing :-

[PhilipTreacy]

Hi @Anonymous 

Those dates are stored in mm/dd/yyyy format, presume you are using dd/mm/yyyy?

In the PQ Editor right clickon the AR Invoice Date column -> Change Type -> Using Locale.

Choose Data Type : Date and Locale : English (United States)

Regards

Phil

[Anonymous]

Hi Phil

Thank you for your advise. I try to create a new pbi file. and the date format is mm/dd/yyyy. see the image below :-

But the field is text , and i need to change to date , and it will work fine , cannot change at PQ.

[PhilipTreacy]

Hi @Anonymous 

Why can't you change it in the PQ Editor as I described?  I don't see why you can't??

Phil

[Anonymous]

Hi Phil

I just load a new file from folder , as you can see from PQ , the date is text , i try to chnage to date :-

you notice that date field get many error :-

Where did it go wrong ?

Paul

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

That image shows you trying to change the date from the Ribbon, I said to right click on the column header and then Change Type -> Using Locale

Regards

Phil

[Anonymous]

Hi Phil

it work fine now. Thank you learn one more thing.

Paul

[mussaenda · Accepted solution]

Hi @Anonymous,

it is showing error because the date format that power bi detecting is dd/mm/yyyy.

Try to change the Regional Setting of the report. Options and Settings->Options->Regional Setting

if it is English UK , format is dd/mm/yyyy

if you select Englisg US , format is mm/dd/yyyy

Hope this helps.

[amitchandak]

@Anonymous , Error is not visible. But seem like data type error. Maybe dates are in two 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