Forum Discussion

OmarSaleh's avatar
OmarSaleh
Helper I
5 years ago
Solved

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. 

 

 

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. 

 

 

 

 

 

 

Need Help
Show and Tell
  • HotChilli's avatar
    HotChilli
    5 years ago

    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

6 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    5 years ago

    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

  • KNP's avatar
    KNP
    Super User
    5 years ago

    Hi OmarSaleh - try conversion using locale.

     

     

     

    = Table.TransformColumnTypes(Source, {{"Column1", type text}}, "en-NZ")
    • OmarSaleh's avatar
      OmarSaleh
      Helper I
      5 years ago

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

       

    • OmarSaleh's avatar
      OmarSaleh
      Helper I
      5 years ago

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