Forum Discussion

LauraBueno's avatar
LauraBueno
Helper III
4 years ago
Solved

Date format in power Bi Desktop

Hi all,

 

I have data table in power bi Desktop that has a column with date information and the format is text. I am trying to convert it to Date format however when I try to change the format it considers US format for some reason. I have set up English (United Kingdom) in both desktop and also PowerBi service so not sure why it is selecting US format 😞

 

Also, something strange that I have notice is that it is mixing both formats so when I have 29/06 it considers UK version (since it cannot use 29 for the month) but otherwise it will use US format for the date.

Any ideas how could I fix this please?

 

Thanks in advance!

Laura

Need Help
  • VIJAYKUMART's avatar
    VIJAYKUMART
    4 years ago

    step 1:

    Step 2:

     

     

    then change data type in power query. Hope it helps

     

4 Replies

  • Adescrit's avatar
    Adescrit
    Impactful Individual
    4 years ago

    Try this:

     

    Date = 
VAR __Day = VALUE( LEFT( 'Table'[Text], 2) )
VAR __Month = VALUE( MID( 'Table'[Text], 4, 2))
VAR __Year = VALUE( RIGHT( 'Table'[Text] , 4) )

RETURN
    DATE( __Year, __Month, __Day )
  • VIJAYKUMART's avatar
    VIJAYKUMART
    Resolver I
    4 years ago

    step 1:

    Step 2:

     

     

    then change data type in power query. Hope it helps

     

    • LauraBueno's avatar
      LauraBueno
      Helper III
      4 years ago

      Hi VIJAYKUMART ,

       

      thanks - I tried that but it didn't help, it still changes the format to US version 😞

    • LauraBueno's avatar
      LauraBueno
      Helper III
      4 years ago

      Actually, a bit strange, but I was changing it within the 'data' tab and didn't work but when I changed it from the power query it works 🙂 so Thanks a lot