Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Create new integer column for text

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 '2...
Tutorial Requests
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    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)
     
     
rajendraongole1's avatar
rajendraongole1
Super User
2 years ago

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