Forum Discussion

Zakaria_1980's avatar
Zakaria_1980
Post Patron
6 years ago

DataFormat.Error: we couldn't convert to Number

Dears,   i was looking to replace( '- ) with ( - ) since in my Excel sheet i have some values with '-70000. the dash caused a problem. when trying to replace it in power query it doesn't allow me ...
Need Help
Zakaria_1980's avatar
Zakaria_1980
Post Patron
6 years ago

Hello,

 

this is the example you have requested. so in case the cell is written with '- it will be just - and delete (') to be converted into number which is below 0. and for date , i want to be displayed as 06/07/2020.

 

ValueDate
'-77000020200705

 

Thanks in advance.

Smauro's avatar
Smauro
Solution Sage
6 years ago
Hi Zakaria_1980

I think this should work for you:
#"Transform Stuff" =Table.TransformColumns(PreviousStep,{{"Value", (t) => Number.FromText(Text.Replace(Text.From(t), "'", "")), type number}, {"Date", Date.From, type date}})

You should change PreviousStep to the previous step of your query.
  • Zakaria_1980's avatar
    Zakaria_1980
    Post Patron
    6 years ago

    please tell me exacetly how can i implement what you have proposed ? i'm beginner in using PBI.

     

    Thanks a lot

     

  • Zakaria_1980's avatar
    Zakaria_1980
    Post Patron
    6 years ago

    below what i go when i added what you have shared with me.

     

     

    below what i have under power query and i've highlighted what you shared with me, just changing the name of the colmn. thanks to help me with this error.

     

    Thanks

    • Smauro's avatar
      Smauro
      Solution Sage
      6 years ago

      You almost got it!

      But you should change #"PreviousStep" to #"Changed Type1", you must have missed the part where I said that 🙂

       

      It should be

          #"Transf.... (#"Changed Type1", ...
in
    #"Transform Stuff"


      Cheers

      • Zakaria_1980's avatar
        Zakaria_1980
        Post Patron
        6 years ago

        Hi,

         

        even with the update, nothing happend (see below)