Remove/replace cells that contain only minus symbol, keep numbers

[Anonymous]

We have "dirty" data, where some cells represent zero values as "-" and some as just 0. 

Power Query can't convert columns with "-" into Numeric data type and throw errors in Query. Ideally I have to replace cells that contain only "-" and "    -" with zero. 
 I can't turn columns into text values and remove "-" symbol: then I would have negative values turned to positives, "-84.21" to "84.21". Do you have an idea how to overcome this? 

[v-xulin-mstf]

Hi @Anonymous,

Is your issue solved?

If the issue has been solved, please adopt the solution to help others.

If you still have some question, please don't hesitate to let me known.‌‌

😉

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[v-xulin-mstf · Accepted solution]

Hi @Anonymous

You can use the Text.End function to replace "-" and "-", then even if you convert the column to text format will not affect the negative value.

Steps:

Convert column into text:

Add custom column as:

if Text.End([Value],1)="-"
then 0
else [Value]

The pbix is attaached.

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[amitchandak]

@Anonymous , remove space, and if you are left out only with - , in that case you can create new column and you can check for value = -

if [col] = "-" then null else [col]

change datatype to number