Date changing to incorrect date regardless of region

[Anonymous]

Hi guys,

Imported some data with a date column, coming in as a string:

When I change the field type to date, it's converting really oddly:

Tried changing the date format based on locale, which didnt work. Which makes sense since the locale would normally switch the month/day order, not completely change it?

Any ideas? The whole column is unfortunately filled with the same value so I don't have any other sample data but throwing this out there incase someone else has experienced it before.

Thanks

[vanessafvg]

@Anonymous that is very weird, dont suppose you could share your pbix?  if not try format trim and clean then convert?

[Anonymous · Accepted solution]

Thanks for replying @vanessafvg.

It turned out to be something a little silly. The dataset behind the query was updated while I was using query editor.

The behavior of the query editor was still very strange, it shouldn't have updated without me pressing refresh, and even if i did, it should have updated all 'steps' of the transformation at the same time. Instead I was able to spend a quite a while deleting and remaking transformation 'steps' which appeared to be changing the data.

Anyway it's all fixed now, if someone else has the same issue they can try checking if the original dataset has changed, even if they didnt refresh the info.