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

[Satch]

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?

[joell001213]

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

[joell001213]

안양풀싸롱[오피투데이(오투)☞☜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]

@Satch,

Could you please share some sample data and expected output?

[Satch]

(ABC)Birthdate                 (123) Output - Age

2001-02-04                      16

1997-12-03                      19

0                                     unknown

0                                     unknown

1980-01-23                      37

0                                     unknown

[SivaMani]

@Satch,

Create a calculated column like below,

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

[Satch]

@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]

@Satch

Use the dax and see if it will meet your requirements

[Satch]

@SivaMani

Yes it does, thanks!

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

[Greg_Deckler]

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

[MarcelBeug · Accepted solution]

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"

[Satch]

@MarcelBeug

Wonderful, thank you!

Also thanks to the other contributors for the given insight