Create new integer column for text

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!!

thank you!