Date format in power Bi Desktop

[LauraBueno]

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

[VIJAYKUMART · Accepted solution]

step 1:

Step 2:

then change data type in power query. Hope it helps

[LauraBueno]

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 

[LauraBueno]

Hi @VIJAYKUMART ,

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

[Adescrit]

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 )