Numbers don't stay numbers after query

[Gillis Beel]

Heyy,

I'm working on automating a system where we copy data from multiple excel workbooks into a overview workbook where we can analyse the data. The excel sheet are filled in by operators all over the factory using different versions of excel and the workbooks stay open on their pc's. At this moment we open the data these workbooks on our pc's and copy the data manually over in the overview. What i wanna do is automate this process. I thought about using excel querry (excel 2016) but i ran into a problem. After the query excel doesn't recognise the numbers as numbers anymore (shifted to the left of the cell). When I manually select a cell and press enter it changes to a number (shifted to the right of the cell). I didn't find any solution online except for adding a colomn for each colomn with numbers where you multiply with 1.
Has anybody any idea how to really solve this? My guess it has something to do with the open workbooks or with the fact they are from a different version of excel.

 

[arunsjain]

Hi,

In power query, change data type of that column to Decimal Number or Whole Number. This should work. If there is any text in that column, you will see error in that column.

Hope it will help.

Cheers!!!

 

[Gillis Beel]

Hey,

Thanks for the reply. This solves indeed the problem for the numbers. But now I have another problem. There is also a column with dates in the table. The dates are converted to a number during the query. But when i try to change their data type to date format it gives an error. I can however change them to an number just the same as the other numbers. I can use it to calculated weeknumbers from it so in practice i got everything i need. But i would be handy that it shows a real date instead of a number. Any help here?

 

[arunsjain]

Hey,

Thanks for the reply. This solves indeed the problem for the numbers. But now I have another problem. There is also a column with dates in the table. The dates are converted to a number during the query. But when i try to change their data type to date format it gives an error. I can however change them to an number just the same as the other numbers. I can use it to calculated weeknumbers from it so in practice i got everything i need. But i would be handy that it shows a real date instead of a number. Any help here?

Hi,

In excel check that data column, is it really a number that can be converted in date format? if not then power query will not convert it into date. Because some times there are some trailing spaces with numbers. In that case some transformation can be applied in power query like TRIM that column before change data type to date. If you could provide some data, let me have a look.

Cheers!!