Solved: Problem in changing format from text to date

OmarSaleh · ‎06-01-2021

Hi,

One of the tables I have is imported from an excel sheet with a column in Date/Time format as shown below.

I need to convert it to Date in power Bi (not excel). The step I'm following are as follows:

1- Trim the colum and keeping the date only (ex. 14/01/2021)

2- Convert format from Text to Date

however, when I do this I get an error because of the regional settings (Locale for import) set on US Time.

Anyone can advise please?

Note: I tried to change the settings for the entire file from Options & Settings --> Options --> Regional Settings, and selected UK Time, but didn't success as after implementing the change I got the error message "loading blocked by failures with other queries" for all the other tables in the file.

I would be grarful if anyone can help.

HotChilli · ‎06-01-2021

right-click on the column, pick 'change type'-> using locale. I can't remember if you need to pick English(uk) or English(u.s.) but one of them will work

View solution in original post

Fowmy · ‎06-01-2021

@OmarSaleh

As pointed out by @HotChilli , Choose English (United Kingdom)

Did I answer your question? Mark my post as a solution! and hit thumbs up

⭕ Subscribe and learn Power BI from these videos

⚪ Website ⚪ LinkedIn ⚪ PBI User Group

KNP · ‎06-01-2021

Hi @OmarSaleh - try conversion using locale.

= Table.TransformColumnTypes(Source, {{"Column1", type text}}, "en-NZ")

Have I solved your problem?
Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;).

If you found this post helpful, please give Kudos.
It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen.
If you find my signature vaguely amusing, please give Kudos.
KIfp67uy-Sr

Proud to be a Super User!

OmarSaleh · ‎06-01-2021

Just found it by right clicking on the column --> change type instead of using the tools bar. Thank you @KNP!

OmarSaleh · ‎06-01-2021

Hi @KNP , I don't have "using locale" option in the dropdown list. Any idea?

HotChilli · ‎06-01-2021

right-click on the column, pick 'change type'-> using locale. I can't remember if you need to pick English(uk) or English(u.s.) but one of them will work