Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 months ago
Solved

DataFormat.Error: We couldn’t convert to Number in Power Query

In Power Query, I’m trying to change a column to Decimal_Number, but I keep getting this error "DataFormat.Error: We couldn't convert to Number"
The column looks like it has numbers, but some rows have things like "-" or blanks. How do I handle this cleanly without breaking my queries ?

Need Help
  • Jai-Rathinavel's avatar
    Jai-Rathinavel
    4 months ago

    Anonymous in Power Query, first replace bad values before changing type. Try the below M Code and see if that helps.

     

    = Table.ReplaceValue(
    PreviousStep,
    "-",
    null,
    Replacer.ReplaceValue,
    {"YourColumn"}
)

     

3 Replies

  • Jai-Rathinavel's avatar
    Jai-Rathinavel
    Super User
    4 months ago

    Anonymous in Power Query, first replace bad values before changing type. Try the below M Code and see if that helps.

     

    = Table.ReplaceValue(
    PreviousStep,
    "-",
    null,
    Replacer.ReplaceValue,
    {"YourColumn"}
)

     

  • Zanqueta's avatar
    Zanqueta
    Super User
    4 months ago

    Hi Anonymous 

     

    My suggestion is to consider a defensive approach that prevents the query from failing even when the column contains unexpected characters such as hyphens or blanks. Power Query allows this through the try ... otherwise construct, which safely handles conversion errors.
     
    Create a New Column with Safe Numeric Conversion
    = try Number.From([YourColumn]) otherwise null