how to force integers into text (they are codes)

[ovonel]

I have some data from excel. There is a column which are codes, most of them just numeric but come are alphanumeric.

When in excel I see the message “the number in this cell is formatted as text or preceded by an apostrophe”

When I open it in powerbi, I consume the file…. I go to “transform data” (instead of load).

Then I go to the said column and when I want to change the datatype to text, I get:

(I have tried both options, and with both options I end up with “errors”).

What is the correct way for forcing even a column that is just numbers into text? ( they are codes, so no intention of using them as numbers)

[v-xiaoyan-msft]

Hi@ovonel,

I notice that there is the message “the number in this cell is formatted as text or preceded by an apostrophe” in excel before you connect to Power BI Desktop.

You can handle this problem first ,  then try it again in Power BI Desktop.

Here's the workround :You cannot convert text to number when the cell contains a number 

Hope it helps.

Best Regards,
Caitlyn Yan

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[PaulDBrown]

@ovonel 

You are getting this message because Power Query "automatically" assigns a field type when the query is loaded. Check in the query steps, locate the first "Changed Type" step and edit the rogue column to tye text