Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
OmarSaleh
Helper I
Helper I

Problem in changing format from text to date

Hi, 

 

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

 

OmarSaleh_0-1622571432849.png

 

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.

OmarSaleh_1-1622571659178.png

 

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. 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

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

6 REPLIES 6
Fowmy
Super User
Super User

@OmarSaleh 

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

Fowmy_0-1622576941195.png

 

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
Super User
Super User

Hi @OmarSaleh - try conversion using locale.

 

KNP_0-1622572814595.png

 

 

= 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 ;).
chrome-9xf-Zagzel-B

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!PBI-Super-User-Rank-30x30-1x

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

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

OmarSaleh_0-1622582686724.png

 

HotChilli
Super User
Super User

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

Thanks @HotChilli. It worked perfectly with English UK. 

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.