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]

Nobody will know what format you show if you use Center Align, especially in the picture
If data is from public website or file doesn't contain any private data, post here a link to the shared .pbix file via OneDrive, GoogleDrive or any similar

 

[murthysydney]

The data set is from the company so could not be sent across. So I have captured the data set and posted for more visibility of errors.

 

[murthysydney]

The data set is from the company so could not be sent across. So I have captured the data set and posted for more visibility of errors.

 

[sandy666]

make a copy of the file
remove everything except these columns you show in table 5 (source)
try option I said in post #2 second line

or try simple Replace Value (right click on this field), Error to proper value in row 296

 

[sandy666]

btw. did you split this to rows?
delimiter can be CR or LF or both

 

[murthysydney]

Hi Mate,

The below is the link of the file as you suggested.
Thanks

Errors.xlsx

drive.google.com

 

[sandy666]

at post#7 : hard to say something without source (intranet or any local network)
at post#6 : I forgot to add example picture, so here is

 

[macfuller]

Without knowing the data source it’s hard to know if the web page has some hidden control characters in the field you want to convert. Can you copy the text directly from the web page into an editor like UltraEdit to see what might be in there?

 

[murthysydney]

Hi Team,

The below is the file in BI - Column 13 gives error when I try to upload, I created the power trim function to find out if there are any spaces to remove it. But did not work.

Errors1.pbix

drive.google.com