cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Satch Member
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
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"}}),
    #"Added Custom" = Table.AddColumn(Source, "BirthDateYYYYMMDD", each if [BirthdateText] = "0" then Date.AddDays(Today,-400) else Date.FromText([BirthdateText]), type date),
    BirthDateYYYYMMDD = Table.TransformColumns(#"Added Custom",{{"BirthDateYYYYMMDD", each 10000 * Date.Year(_) + 100 * Date.Month(_) + Date.Day(_)}}),
    AddedAge = Table.AddColumn(BirthDateYYYYMMDD, "Age", each Number.IntegerDivide(TodayYYYYMMDD-[BirthDateYYYYMMDD],10000), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(AddedAge,{"BirthDateYYYYMMDD"})
in
    #"Removed Columns"
Specializing in Power Query Formula Language (M)
11 REPLIES 11
SivaMani Senior Member
Senior Member

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

@Satch,

 

Could you please share some sample data and expected output?

Satch Member
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

joell001213 Member
Member

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

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

안양풀싸롱[오피투데이(오투)☞☜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#플보☞☜]#아밤 #아찔한밤 #밤전 #오피뷰ψ

SivaMani Senior Member
Senior Member

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

@Satch,

 

Create a calculated column like below,

 

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

joell001213 Member
Member

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

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

간석휴게텔[오피투데이(오투)☞☜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#플보☞☜]#아밤 #아찔한밤 #밤전 #오피뷰ψ

Satch Member
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?

SivaMani Senior Member
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
Satch Member
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
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

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!