Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have a text column with a mix of both decimal and alphanumeric values. While importing, leading zeroes are truncated from decimal values. I need to pad only the decimal values to specific length but leave the alphanumeric values unchanged. Tried Text.Padstart but it's changing for all values in that column. Data is as below.
Expected sample output:
Values starting with G - unchanged
G.025 -> G.025
6.010 -> 06.010
Any quick help is appreciated.
Solved! Go to Solution.
Hi, @AmudhaKumaran
Table.TransformColumns(your_table, {"Code", (x) => if (try Number.From(x))[HasError] then x else Text.PadStart(x, 6, "0"})
You could use a replace value step
Copy this into a new blank query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctczMDJQitVBZ5mSxDLTMzA0gLMMMVkGcHUGFkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t]),
ReplValue = Table.ReplaceValue( Source, each [Code], each if (try Number.From([Code]))[HasError] then [Code] else Text.PadStart( Text.From( [Code] ), 6, "0"), Replacer.ReplaceText, {"Code"} )
in
ReplValue
Ps. If this helps solve your query please mark this post as Solution, thanks!
You could use a replace value step
Copy this into a new blank query
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WctczMDJQitVBZ5mSxDLTMzA0gLMMMVkGcHUGFkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t]),
ReplValue = Table.ReplaceValue( Source, each [Code], each if (try Number.From([Code]))[HasError] then [Code] else Text.PadStart( Text.From( [Code] ), 6, "0"), Replacer.ReplaceText, {"Code"} )
in
ReplValue
Ps. If this helps solve your query please mark this post as Solution, thanks!
Hi, @AmudhaKumaran
Table.TransformColumns(your_table, {"Code", (x) => if (try Number.From(x))[HasError] then x else Text.PadStart(x, 6, "0"})