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.
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?
Solved! Go to 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"
간석휴게텔[오피투데이(오투)☞☜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#플보☞☜]#아밤 #아찔한밤 #밤전 #오피뷰ψ
안양풀싸롱[오피투데이(오투)☞☜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#플보☞☜]#아밤 #아찔한밤 #밤전 #오피뷰ψ
(ABC)Birthdate (123) Output - Age
2001-02-04 16
1997-12-03 19
0 unknown
0 unknown
1980-01-23 37
0 unknown
Create a calculated column like below,
Age= IF(Table1[(ABC)Birthdate] <> "0",DATEDIFF(DATEVALUE(Table1[(ABC)Birthdate]),TODAY(),YEAR),BLANK())
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?
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
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"
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 |
---|---|
113 | |
99 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |