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
Mariusz's avatar
Mariusz
Community Champion
6 years ago

Hi Zakaria_1980 

 

Can you create a sample and past it as a table into the body of the post, with both examples?

 

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

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