Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
OmarSaleh
Helper I
Helper I

Problem in changing format from text to date

‎06-01-2021 11:26 AM

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. 

 

 

 

 

 

 

Labels:
Message 1 of 7
598 Views
0
1 ACCEPTED SOLUTION
HotChilli
Super User
Super User

‎06-01-2021 11:37 AM

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

Message 2 of 7
569 Views
6 REPLIES 6
Fowmy
Super User
Super User

‎06-01-2021 12:49 PM

@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

Message 7 of 7
538 Views
0
KNP
Super User
Super User

‎06-01-2021 11:41 AM

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
Message 4 of 7
569 Views
OmarSaleh
Helper I
Helper I
In response to KNP

‎06-01-2021 02:28 PM

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

Message 6 of 7
520 Views
0
OmarSaleh
Helper I
Helper I
In response to KNP

‎06-01-2021 02:24 PM

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

OmarSaleh_0-1622582686724.png

 

Message 5 of 7
524 Views
0
HotChilli
Super User
Super User

‎06-01-2021 11:37 AM

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

Message 2 of 7
570 Views
OmarSaleh
Helper I
Helper I
In response to HotChilli

‎06-01-2021 02:27 PM

Thanks @HotChilli. It worked perfectly with English UK. 

 

 

Message 3 of 7
522 Views
0

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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

View All