Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Satch
Helper III
Helper III

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

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)

View solution in original post

11 REPLIES 11
joell001213
Helper II
Helper II

간석휴게텔[오피투데이(오투)☞☜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
Helper II
Helper II

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

@Satch,

 

Could you please share some sample data and expected output?

(ABC)Birthdate                 (123) Output - Age

 

2001-02-04                      16

1997-12-03                      19

0                                     unknown

0                                     unknown

1980-01-23                      37

0                                     unknown

SivaMani
Resident Rockstar
Resident Rockstar

@Satch,

 

Create a calculated column like below,

 

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

@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
Resident Rockstar
Resident Rockstar

@Satch

Use the dax and see if it will meet your requirements

@SivaMani

 

Yes it does, thanks!

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

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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)

@MarcelBeug

 

Wonderful, thank you!

Also thanks to the other contributors for the given insight

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.