Create new integer column for text

[Anonymous]

Hi, I have this set of data, now i would like to derive a new column call age:

For YOB type is text, so im encounter problem to convert it to number. 

For new column Age it should be derive from '2024-YOB'

YOB	AGE
1990	34
19DC	0
1972	52
1983	41
19DC	0
19DC	0

[Anonymous]

thank you!

[ryan_mayu · Accepted solution]

@Anonymous 

you can also try to use DAX to get the new column

AGE = IFERROR(2024-'Table'[YOB],0)

OR 

AGE = IFERROR(YEAR(TODAY())-'Table'[YOB],0)

[Anonymous]

thank you!

[ryan_mayu]

you are welcome

[rajendraongole1]

Hi @Anonymous -you can add one new add column in power query editor as below:

if [YOB] = 0 then 0 else 2024 - [YOB]

you will have a new Age column derived from the YOB column, with invalid YOB values handled appropriately.

M code:

let
Source = Excel.Workbook(File.Contents("C:\Path\To\Your\File.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"YOB", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","19DC","0",Replacer.ReplaceText,{"YOB"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value",{{"YOB", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Age", each if [YOB] = 0 then 0 else 2024 - [YOB]),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom",{{"Age", Int64.Type}})
in
#"Changed Type2"

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!