External Data (from excel)-Number format as text

I've been using this forum for years and it's the first time I couldn't find what I've been looking for (love it)-maybe I searched the wrong terms.

I am trying to connect to an external data source via microsoft query. The source I am connecting to is a scheduled excel file that is saved over/updated each night. The problem I'm having is one of my columns is importing in as text when is should be a number. I find it strange as I have a very similar column that comes thru correctly. Importing from an excel file is very new to me. Is there a way to define what the column formats should be? I know how to do text to column, however the next time I refresh the data it is again changed to text.

Thanks for any guidance you can give!

Heidi

Thanks Sambo-however I should add I am attempting to bring the data in directly to a pivot table-I could probably do what you suggested in a calculated field if need be but wondering if there's a way within the query itself.