Advanced › try, otherwise, Number.FromText45 / 47

Progress is saved on this device. Sign in or sign up free to keep it in your account.

EasyFreetryotherwiseNumber.FromText

try ... otherwise: free-text ages from a survey

Mega, who owns a cafe in Malang, ran a satisfaction survey with Google Forms. The age field was free text, so there are answers like "dua puluh" (twenty), "-" and "40 thn" (40 yrs). Converted to numbers, those cells become Errors and break the average age calculation.

Task

Add an UmurAngka (numeric age) column with Table.AddColumn: its value is Number.FromText([Umur]), wrapped in try ... otherwise null so answers that cannot be converted become null instead of Error. Result: 4 columns Responden, Umur, Rating, UmurAngka and 7 rows in the source order.

Data sourcesSurvei

Required functionstryotherwiseNumber.FromText

Expected result

Loading the expected result…

Check answerCtrlShiftEnter
Run previewCtrlEnter
Previous / next exerciseAlt←→
Leave the workspaceEscTab

Hint 1
try expression otherwise fallback evaluates the expression; if it raises an error, the fallback value is used instead.
Hint 2
Put try right in front of the part that can fail, Number.FromText([Umur]), inside each.
Hint 3
#"Added UmurAngka" = Table.AddColumn(Source, "UmurAngka", each try ___ otherwise ___)

Reveal them one at a time. The last hint nearly gives the answer away.

Give it a try first. The solution unlocks automatically after your first check.

Answer

let
    Source = Survei,
    #"Added UmurAngka" = Table.AddColumn(Source, "UmurAngka", each try Number.FromText([Umur]) otherwise null, type nullable number)
in
    #"Added UmurAngka"

Explanation

"25", "31" and "19" convert to numbers. "dua puluh", "-", "40 thn" and the empty text make Number.FromText fail, and try ... otherwise null catches that error and returns null instead. Aggregations such as List.Average ignore nulls, so the average age can still be computed from the valid answers.
Use otherwise null, not otherwise 0: zeros would drag the average age down. Tip: keep the original Umur column next to the new one so failed answers can be reviewed and fixed by hand. Without otherwise, try returns a record with HasError and the error details, which is handy for building an error log.

Power Query· sources: Survei

let
    Source = Survei
in
    Source

Loading the Advanced Editor…

Edit the M code until the result matches, then press Check answer.

Validation result