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 to do it. do you have any other idea how to do in power Query

 

i have also in some columns date in this format 20170410, how can i change the colmn so the date will looks like 10/04/2017?

 

br,

 

Need Help

8 Replies

  • 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.