Forum Discussion

AmudhaKumaran's avatar
AmudhaKumaran
Helper I
3 years ago
Solved

Power Query - Data type

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.

Need Help
  • AlienSx's avatar
    AlienSx
    3 years ago

    Hi, AmudhaKumaran 

    Table.TransformColumns(your_table, {"Code", (x) => if (try Number.From(x))[HasError] then x else Text.PadStart(x, 6, "0"})
  • m_dekorte's avatar
    m_dekorte
    3 years ago

    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!

4 Replies

Replies have been turned off for this discussion
  • AlienSx's avatar
    AlienSx
    Super User
    3 years ago

    Hi, AmudhaKumaran 

    Table.TransformColumns(your_table, {"Code", (x) => if (try Number.From(x))[HasError] then x else Text.PadStart(x, 6, "0"})
  • m_dekorte's avatar
    m_dekorte
    Resident Rockstar
    3 years ago

    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!