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 leng...
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!

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!