Power Query - Data type

[AmudhaKumaran]

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.

[AlienSx · Accepted solution]

Hi, @AmudhaKumaran 

Table.TransformColumns(your_table, {"Code", (x) => if (try Number.From(x))[HasError] then x else Text.PadStart(x, 6, "0"})

[m_dekorte · Accepted solution]

Hi @AmudhaKumaran 

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!

[AmudhaKumaran]

Thanks for your help @m_dekorte . It works fine.

[AlienSx · Accepted solution]

Hi, @AmudhaKumaran 

Table.TransformColumns(your_table, {"Code", (x) => if (try Number.From(x))[HasError] then x else Text.PadStart(x, 6, "0"})

[AmudhaKumaran]

Thanks @AlienSx for your swift response. Solution works great.