Datatype

[Anonymous]

When im trying to change my datatype from ABC123 to whole numbers it doesnt work. There was a row with text there before but i replaced the data with 0. The query has been appended before so i tried to replace the row with the text in the previous query as well. This didnt work. Is there a way for me to change the datatype without starting over? 

[edhans · Accepted solution]

1. Do your transformation to change from ABC123 to Integer.
2. On the Home ribbon, select the Keep Rows dropdown and "Keep Errors"
3. Put your cursor next to the word "error" (don't click on the word "error") and look at the yellow box at the bottom of the screen.

It will tell you why it is having trouble. You can then clean that up as well before you do the ABC123-Integer conversion. In my sample below I entered the text "aaa" in a column of numbers.

[v-lid-msft]

Hi @Anonymous ,

If the column has the same sub-String such as "ABC", we can use Table.ReplaceValue to remove it and then change it to whole number.

Best regards,

[HotChilli]

What error message are you getting?

If you use the View menu->Column Quality you can track these issues down.

Also, at the bottom of the screen, profiling can be changed to look at the whole dataset rather than the first 1000 rows