Data Imported from Web ---NOT able to convert to Number or Text or any format -To remove errors

[murthysydney]

Hi All,

I have data extracted from the web which I am trying to change the data type to Text or Number or any other format to remove errors. I cannot delete it as it contains data. Tried to use clean, trim and then trying to convert still getting errors for few of the rows which have multiple values
I have cleaned and Trim and split columns to extract the numbers but still getting errors when I load and close. Can you suggest any other options?
Thanks

 

[sandy666]

try to post a link to the file containing error and source data

M-code doesn't contain any error
and use [CODE ] your M-code [/CODE ] (tags without spaces)

 

[murthysydney]

Hi Team,

I am attaching the file with the errors I have used trim clean and try to remove white spaces still getting an error, the format can be text or number but all the errors have numbers in it so I cannot ignore the errors.

Error In Number.pbix

drive.google.com

Thanks

 

[sandy666]

You have access to the server - I don't
so

 

[sandy666]

try to remove this line from fxQuery1
#"Changed Type" = Table.TransformColumnTypes(Data5,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type text}, {"Column4", type date}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", Int64.Type}, {"Column13", Int64.Type}, {"Column14", Int64.Type}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}})

 

[murthysydney]

Hi Sandy,

I have change in fxquery1 there is no error during the referesh but during the close and apply i have recived the same error.
When i click on error the below is the message. I have tried to convert to number did not work than to text did not work.

DataFormat.Error: We couldn't convert to Number.
Details:
12572
12055

 

[sandy666]

probably because on the start/end of specific values you have #(lf) or any Unicode character: 127, 129, 141, 143, 144, 157, and 160
check LEN for this column
try to remove first character and check LEN
try to remove last character and check LEN
or
split column by number of characters with 1 as parameter, if you get blank column that means there is any hidden character

 

[murthysydney]

Hi Sandy
There was a Unicode character hidden text.

Thanks

 

[sandy666]

Welcome