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