Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Date format

Hello everybody,   I have a question about my data. In my data a have a column called Birth date where all have this format :  "Thu Dec 31 1964 15:00:00 GMT-0800 (Pacific Standard Time)" ..... "...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi can you try these steps? It does seem to work for me:

    1) in Power BI click on Edit Queries in the Home Tab

    2) Look for the birthdate column, click on it

    3) Go to the Transform tab and click on Split Column

    4) Choose "By delimiter" and select "Custom" in the dropdown menu that pops up

    5) In the textfield below the dropdown, type in "GMT"

    6) Pick "left-most delimiter"

    7) Press OK

    8.) Power BI will split your data in two different columns named Birthdate.1 and Birthdate.2

    Birthdate.1 will be transform to a Date/Time type.

    9) Don't forget to go to the home tab and press Close&Apply

     

    These steps will result in the following DateTime column

     

    Let me know if this works for you.

     

     

    Birthdate.1

    31-12-1989 15:00:00
    31-12-1989 15:00:00
    31-12-1949 15:00:00
    31-12-1989 15:00:00
    31-12-1989 15:00:00
    31-12-1989 15:00:00
    31-12-1969 15:00:00
    31-12-1959 15:00:00
    31-12-1969 15:00:00
    31-12-1989 15:00:00
    31-12-1974 15:00:00
    31-12-1988 15:00:00
    31-12-1969 15:00:00
    31-12-1993 15:00:00
    31-12-1959 15:00:00
    31-12-1979 15:00:00
    31-12-1988 15:00:00
    31-12-1998 15:00:00
    31-12-1992 15:00:00
    31-12-1949 15:00:00
    31-12-1969 15:00:00
    31-12-1959 15:00:00
    31-12-1989 15:00:00
    31-12-1987 15:00:00
    31-12-1969 15:00:00
    31-12-1964 15:00:00
    31-12-1969 16:00:00
    22-5-2001 17:00:00
    31-12-1979 16:00:00
    31-12-1994 16:00:00
    31-12-2018 16:00:00
    31-12-1989 16:00:00
    10-7-1979 17:00:00
    17-8-1971 17:00:00
    31-12-2018 16:00:00
    19-8-1979 17:00:00
    31-12-1989 16:00:00
    31-12-1938 16:00:00
    31-12-2018 16:00:00
    16-12-2019 16:00:00
    16-12-2019 16:00:00
    16-12-2019 16:00:00
    19-12-2019 16:00:00
    31-12-2018 16:00:00
    25-12-2019 16:00:00
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi Anonymous ,

     

    Try the following DAX, I think region is the problem.

    Column = 
var a =SEARCH(" ",'Table'[birthDate],1)+1
var b = SEARCH(" ",'Table'[birthDate],5)+1
var c = SEARCH(" ",'Table'[birthDate],9)+1
var d = MID('Table'[birthDate],a,3)
var e = SWITCH(
    TRUE(),
    d="Dec",12,
    d="Nov",11,
    d="Oct",10,
    d="Sep",9,
    d="Aug",8,
    d="Jul",7,
    d="Jun",6,
    d="May",5,
    d="Apr",4,
    d="Mar",3,
    d="Feb",2,
    d="Jan",1
)
return
IF(c=12,
MID('Table'[birthDate],b,2),
0&MID('Table'[birthDate],b,1)
)
&"-"&e&"-"&MID('Table'[birthDate],c,4)

    Here is the test file for your reference.

     

     

v-eachen-msft's avatar
v-eachen-msft
Community Support
6 years ago

Hi Anonymous ,

 

Try this column:

Column =
VAR a =
    SEARCH ( " ", 'Table'[birthDate], 1 ) + 1
VAR b =
    SEARCH ( " ", 'Table'[birthDate], 5 ) + 1
VAR c =
    SEARCH ( " ", 'Table'[birthDate], 9 ) + 1
RETURN
    IF (
        c = 12,
        MID ( 'Table'[birthDate], b, 2 ),
        0 & MID ( 'Table'[birthDate], b, 1 )
    ) & "-"
        & MID ( 'Table'[birthDate], a, 3 ) & "-"
        & MID ( 'Table'[birthDate], c, 4 )

Here is my test file for your reference.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi v-eachen-msft 

     

    thank you for your answer I have a small problem, how can I share my sample pbix with this Forum I didn't find it ?

     

    Thank you