DataFormat.Error: we couldn't convert to Number

[Zakaria_1980]

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,

[Mariusz]

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]

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.

Value	Date
'-770000	20200705

Thanks in advance.

[Smauro]

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]

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]

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]

Hi,

even with the update, nothing happend (see below)

[Zakaria_1980]

please can someone help.

thanks

[Zakaria_1980]

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

Thanks a lot