Excel data type issues

Question

I am using MS query to pull data from sql server and all is good. Problem starts when data comes from the server I am stuck with data type general for everything, and no way to change the data type in excel. Main issue is numbers, where in database datatype is decimal yet i can do no calculations on it in excel. Any help would be appreciated.

I am using excel to execute a stored procedure on server

This pulls the data into the following table

Even though the data in the sql server for column price is formatted as decimal it becomes a general data type after getting to excel. Changing it to number/currency etc. does not change anything. Also no errors appear. Simply data comes down and no matter what changes in excel I apply nothing changes it all is treated as text.

Answer 1

You can do these things.

1. Select Column
2. Click Data-> Text to Columns
3. Follow the wizard
4. Set the format

Use this official support ticket from Microsoft

Answer 2

Problem in this case was created by myself. But I suppose it could easily happen to others who are just starting on their path with sql and excel.

Here is what happened as I established after few days of going in circles. as there was load of trailing spaces in the data coming down from the server I have decided to tidy things up. Without considerring implications I have stuck an RTRIM() on everything. This caused excel to treat everything as strings as string RTRIM is a built in string function.

What made things worse is the fact that when using power query I was able to transform the data to the desired, formats.

Unfortunately MS query does not seem to be quite as clever as power query hence the issues.