Any idea how to clean up this weird number?

[masplin]

The price is coming out as 

�179

Instead of £179.  I've tried the Clean and trim options on right clck. I've tried a custom column using Text.Clean and Text.Trim, but cannot get rid of it. i'm happy to turn thme to null, but too many to do a replace 

I have to convert this column into numbers and all of these throw an error.  I can see any way to use try...otherwise in the conversion to put a null if it errors.

Any suggestions appreciated

Mike

[stretcharm]

All the £ chars I've tried work so I'm not sure what's happening to this one.

I put it another char and it showed the UTF Code when I got an error converting to a number. Click on of the errors to see the details.

If your able to find the character then you migh be able to replace it.

Other options are removed the 1st char if it's always this char use Text.Start

Or try split by delim. This often defaults to a char that might be a good delimiter.

[masplin]

Unfortuantley 99% of entries are correct and £ sign. i have no idea what this symbol is and impossible to search for it on google!!!

[stretcharm]

turn it into a number

then click one of the errors to see what no char it it. I tested with 189.

You should be able to replace this

= Table.ReplaceValue(#"Added Custom",Character.FromNumber(189),"",Replacer.ReplaceText,{"baddata - Copy.1"})

[masplin]

I'm actually doing thisii in power query for excel so dont see the same error message . Just shows me the same funny ? 179. Is 189 the charachter number for the funny "?" or is that for some other charachter you tested with ?

I dont understand the code at the bottom. Is this a line in my power query advanced editor?  Does it add the copy.1 column.  i have 4 columns with this issue

Thanks

Mike

[stretcharm]

189 was just the number I picked to test. the ? could be many chars or even the result of some bad conversion.

If excel doesn't show the error try loading into powerbi.

The code was just a change to the normal replace command "baddata - Copy.1" was just my column.

If you replace x with nothing you'd get something like this.

= Table.ReplaceValue(Source,"x","",Replacer.ReplaceText,{"YourColumnName"})

I just replaced "x" with Character.FromNumber(189)

= Table.ReplaceValue(Source ,Character.FromNumber(189),"",Replacer.ReplaceText,{"YourColumnName"})

You could load a sample into a hex editor like. This will show you what chars are before the 179

https://hexed.it/

I found another £ which is a unicode char ₤ which is the old Lira symbol

https://www.compart.com/en/unicode/U+20A4

I was able to just copy and paste into a replace

= Table.ReplaceValue(Source,"₤","",Replacer.ReplaceText,{"YourColumnName"})

[MFelix]

Hi @masplin,

What is the base for your data? Are you getting the information from a CSV file or text?

If so you should change the File origin to none.

Regards,

MFelix

[masplin]

Its a csv. What do you mena change file origin to none please?

I actually worked out a way of doing this by creating a customer column with a try Number.From otherwise 0. bit inellgant but worked.

[MFelix]

Hi @masplin,

When selecting the source you can choose the File origin type.

This is made in power query.

Regards,

MFelix