cancel
Showing results for
Did you mean:
Member

Age from birthdate column type text, with "0" in it

Hi,

I have a text column with birthdates in it, but also "0" values.

Converting the type to date gives error on the 0

How can I get the age for each birthdate, where age is 1 when there's no birthdate?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

Re: Age from birthdate column type text, with "0" in it

Agree with @Greg_Deckler

My suggested approach for age calculation in Power Query is to convert all dates to numerical values YYYYMMDD (e.g. today 20170923). With these formats, subtract the birthdates from todays date and integer-divide by 10,000.

For the "0" values, 400 days are subtracted from todays date, which will result in age 1.

```let
Today = DateTime.Date(DateTime.LocalNow()),
TodayYYYYMMDD = 10000*Date.Year(Today)+100*Date.Month(Today)+Date.Day(Today),

Source = #table(type table[BirthdateText = text],{{"04/21/1962"},{"12/17/1962"},{"0"},{"10/13/1990"},{"02/29/1988"}}),
BirthDateYYYYMMDD = Table.TransformColumns(#"Added Custom",{{"BirthDateYYYYMMDD", each 10000 * Date.Year(_) + 100 * Date.Month(_) + Date.Day(_)}}),
in
#"Removed Columns"```
Specializing in Power Query Formula Language (M)
11 REPLIES 11
Senior Member

Re: Age from birthdate column type text, with "0" in it

Could you please share some sample data and expected output?

Member

Re: Age from birthdate column type text, with "0" in it

(ABC)Birthdate                 (123) Output - Age

2001-02-04                      16

1997-12-03                      19

0                                     unknown

0                                     unknown

1980-01-23                      37

0                                     unknown

Member

Re: Age from birthdate column type text, with "0" in it안양풀싸롱[오피투데이(오투)☞☜OptODAY2.Com#플보☞☜]#아찔한밤 #밤전

안양풀싸롱[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳안양풀싸롱[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
안양풀싸롱[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳안양풀싸롱[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
안양풀싸롱[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳안양풀싸롱[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
안양풀싸롱[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳안양풀싸롱[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
안양풀싸롱[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳안양풀싸롱[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
안양풀싸롱[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳안양풀싸롱[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
안양풀싸롱[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳안양풀싸롱[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
안양풀싸롱[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳안양풀싸롱[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤

안양풀싸롱[오피투데이(오투)☞☜OptODAY2.Com#플보☞☜]#아밤 #아찔한밤 #밤전 #오피뷰ψ

Senior Member

Re: Age from birthdate column type text, with "0" in it

Create a calculated column like below,

Age= IF(Table1[(ABC)Birthdate] <> "0",DATEDIFF(DATEVALUE(Table1[(ABC)Birthdate]),TODAY(),YEAR),BLANK())

Member

Re: Age from birthdate column type text, with "0" in it간석휴게텔[오피투데이(오투)☞☜OptODAY2.Com#플보☞☜]#아찔한밤 #밤전

간석휴게텔[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳간석휴게텔[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
간석휴게텔[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳간석휴게텔[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
간석휴게텔[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳간석휴게텔[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
간석휴게텔[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳간석휴게텔[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
간석휴게텔[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳간석휴게텔[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
간석휴게텔[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳간석휴게텔[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
간석휴게텔[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳간석휴게텔[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤
간석휴게텔[오피투데이(오투)☞☜OptODAY2.Com플보☞☜]밤전➳밤전➳오피➳아밤➳간석휴게텔[오피투데이(오투) ☞☜OptODAY2.Com플보 ☞☜]아찔한밤 밤전 오피뷰 아밤

간석휴게텔[오피투데이(오투)☞☜OptODAY2.Com#플보☞☜]#아밤 #아찔한밤 #밤전 #오피뷰ψ

Member

Re: Age from birthdate column type text, with "0" in it

@SivaMani

But the birthday column is a text field, so it should also convert to date field to get the right date.

Do you also have the formula when I want to do it in the query editor?

Senior Member

Re: Age from birthdate column type text, with "0" in it

@Satch

Use the dax and see if it will meet your requirements
Member

Re: Age from birthdate column type text, with "0" in it

@SivaMani

Yes it does, thanks!

Now I want to do that in the queryeditor but datediff is not recognized?

Super User

Re: Age from birthdate column type text, with "0" in it

Trying to do date manipulation in M code generally ends in sadness. Manipulation of and calculations around dates are one of the things that seems to be so much simpler in DAX versus M. That being said, you will probably want to start here:

https://msdn.microsoft.com/en-us/library/mt296606.aspx