Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)"
.....
"Sun Aug 19 1979 15:00:00 GMT-0700 (Pacific Standard Time)"
What I want is to creat a new column of birth date where be in this format "31/12/1964" and 19/08/1979
and then to calculate the age.
How can I do this ?
Thank you.
Solved! Go to Solution.
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 |
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.
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.
Hi @Anonymous
I think all above solutinons are correct.
Just remove that GMT part from EDIT query-> modelling tab.
and then calculate AGE differance by using Yearfrac.
Don't use datediff as it doesn't give you correct DOB.
Thanks & regards,
Pravin Wattamwar.
https://www.linkedin.com/in/pravin-p-wattamwar/
If I resolve your problem mark it as solution and give kudos.
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.
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
Hi @Anonymous ,
You could upload your files to OneDrive and share the link here.
Thank you.
You find attached my sample.
I think the problem that is two defferents message :
"Sun Dec 31 1989 15:00:00 GMT-0800 (Pacific Standard Time)"
"Tue Aug 17 1971 17:00:00 GMT-0700 (Pacific Daylight Time)"
Best regards,
Hi @Anonymous ,
Your file has limitation so I cannot download it.
You need to set it like the following image:
Sorry I didn't take care :
Try creating a custom column with the power query editor instead and see if you get the same error
The code would be something like
Text.BetweenDelimiters([BirthDate]," ", " ",1,0) & "/" &
Text.BetweenDelimiters([BirthDate]," ", " ",0,0) & "/" &
Text.BetweenDelimiters([BirthDate]," ", " ",2,0)
then change the type
I don't know why but isn't work. |
BirthDate |
Sun Dec 31 1989 15:00:00 GMT-0800 (Pacific Standard Time) |
Sun Dec 31 1989 15:00:00 GMT-0800 (Pacific Standard Time) |
Sat Dec 31 1949 15:00:00 GMT-0800 (Pacific Standard Time) |
Sun Dec 31 1989 15:00:00 GMT-0800 (Pacific Standard Time) |
Sun Dec 31 1989 15:00:00 GMT-0800 (Pacific Standard Time) |
Sun Dec 31 1989 15:00:00 GMT-0800 (Pacific Standard Time) |
Wed Dec 31 1969 15:00:00 GMT-0800 (Pacific Standard Time) |
Thu Dec 31 1959 15:00:00 GMT-0800 (Pacific Standard Time) |
Wed Dec 31 1969 15:00:00 GMT-0800 (Pacific Standard Time) |
Sun Dec 31 1989 15:00:00 GMT-0800 (Pacific Standard Time) |
Tue Dec 31 1974 15:00:00 GMT-0800 (Pacific Standard Time) |
Sat Dec 31 1988 15:00:00 GMT-0800 (Pacific Standard Time) |
Wed Dec 31 1969 15:00:00 GMT-0800 (Pacific Standard Time) |
Fri Dec 31 1993 15:00:00 GMT-0800 (Pacific Standard Time) |
Thu Dec 31 1959 15:00:00 GMT-0800 (Pacific Standard Time) |
Mon Dec 31 1979 15:00:00 GMT-0800 (Pacific Standard Time) |
Sat Dec 31 1988 15:00:00 GMT-0800 (Pacific Standard Time) |
Thu Dec 31 1998 15:00:00 GMT-0800 (Pacific Standard Time) |
Thu Dec 31 1992 15:00:00 GMT-0800 (Pacific Standard Time) |
Sat Dec 31 1949 15:00:00 GMT-0800 (Pacific Standard Time) |
Wed Dec 31 1969 15:00:00 GMT-0800 (Pacific Standard Time) |
Thu Dec 31 1959 15:00:00 GMT-0800 (Pacific Standard Time) |
Sun Dec 31 1989 15:00:00 GMT-0800 (Pacific Standard Time) |
Thu Dec 31 1987 15:00:00 GMT-0800 (Pacific Standard Time) |
Wed Dec 31 1969 15:00:00 GMT-0800 (Pacific Standard Time) |
Thu Dec 31 1964 15:00:00 GMT-0800 (Pacific Standard Time) |
Wed Dec 31 1969 16:00:00 GMT-0800 (Pacific Standard Time) |
Tue May 22 2001 17:00:00 GMT-0700 (Pacific Daylight Time) |
Mon Dec 31 1979 16:00:00 GMT-0800 (Pacific Standard Time) |
Sat Dec 31 1994 16:00:00 GMT-0800 (Pacific Standard Time) |
Mon Dec 31 2018 16:00:00 GMT-0800 (Pacific Standard Time) |
Sun Dec 31 1989 16:00:00 GMT-0800 (Pacific Standard Time) |
Tue Jul 10 1979 17:00:00 GMT-0700 (Pacific Daylight Time) |
Tue Aug 17 1971 17:00:00 GMT-0700 (Pacific Daylight Time) |
Mon Dec 31 2018 16:00:00 GMT-0800 (Pacific Standard Time) |
Sun Aug 19 1979 17:00:00 GMT-0700 (Pacific Daylight Time) |
Sun Dec 31 1989 16:00:00 GMT-0800 (Pacific Standard Time) |
Sat Dec 31 1938 16:00:00 GMT-0800 (Pacific Standard Time) |
Mon Dec 31 2018 16:00:00 GMT-0800 (Pacific Standard Time) |
Mon Dec 16 2019 16:00:00 GMT-0800 (Pacific Standard Time) |
Mon Dec 16 2019 16:00:00 GMT-0800 (Pacific Standard Time) |
Mon Dec 16 2019 16:00:00 GMT-0800 (Pacific Standard Time) |
Thu Dec 19 2019 16:00:00 GMT-0800 (Pacific Standard Time) |
Mon Dec 31 2018 16:00:00 GMT-0800 (Pacific Standard Time) |
Wed Dec 25 2019 16:00:00 GMT-0800 (Pacific Standard Time) |
Make sure the column is text
New date = mid(dateBirth[BirthDate],9,2) & "-" & mid(dateBirth[BirthDate],5,3) & "-" & mid(dateBirth[BirthDate],12,4)
check pbix https://www.dropbox.com/s/nwjzy01u4czu9uw/Dateformatissue.pbix?dl=0
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
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 |
Hi,
I can't access the link, but you can try to split the column with the date format.
If you split it at the "(" Power BI might be able to understand that it's a date.
You can do this in Power BI Desktop => Edit Queries => Transform tab => Split column => By delimiter.
Kind regards.
Sorry didn't work !
I tried it myself with the two dates you entered before.
If you also remove the GMT part, Power BI will automatically set it as a Date.
Kind Regards
New format = format(date,"DD/MM/YYYY")
For age take date diff. Prefer orginal column in date format
Age = datediff(date,today(),YEAR)
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601
HI amitchandak,
Before to calculate the age how can I transfer this type :
"Thu Dec 31 1964 15:00:00 GMT-0800 (Pacific Standard Time)" to 31/12/1964 ?
Thank you.
Hi,
Can you try going to the column where you store the birtdate, then select it.
Go to the modeling tab and select Format in the Formatting Area.
There you should be able to pick the formatting you want.
Yes, I did this, but it not work.
The software can not read this type of format "Thu Dec 31 1964 15:00:00 GMT-0800 (Pacific Standard Time)", and even if I choose format date in the modeling, i have a error.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |