Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi community
Is there a possibility to replace letters in a column with "null"?
I have a column that contains rows with letters and rows with numbers. I want to replace only rows with letters with a null value.
e.g.
ABC
123
DEF
234
should result in
null
123
null
234
Solved! Go to Solution.
Hi @umaluagr, add this as a new step.
Replace
= Table.ReplaceValue(Source,
each try Number.From([Column1]) otherwise false is number,
each null,
(x,y,z)=> if y then x else z,
{"Column1"} )
Whole code with sample data:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVorViVYyNDIG045OzmDaxNRMKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Replaced Value" = Table.ReplaceValue(Source,
each try Number.From([Column1]) otherwise false is number,
each null,
(x,y,z)=> if y then x else z,
{"Column1"} )
in
#"Replaced Value"
Hi @umaluagr, add this as a new step.
Replace
= Table.ReplaceValue(Source,
each try Number.From([Column1]) otherwise false is number,
each null,
(x,y,z)=> if y then x else z,
{"Column1"} )
Whole code with sample data:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKVorViVYyNDIG045OzmDaxNRMKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Replaced Value" = Table.ReplaceValue(Source,
each try Number.From([Column1]) otherwise false is number,
each null,
(x,y,z)=> if y then x else z,
{"Column1"} )
in
#"Replaced Value"