Errors when importing data through Get&Transform

A

albertan

Member
Joined
Nov 9, 2017
Messages
30
Reaction score
0
Points
6
Location
Canada
Excel Version(s)
2013
I have Office 365's Excel 2016. When I try to import data to Excel I get some errors related for formatting of cells. Sometimes I get errors that say "cannot convert to number".
When I import the data, I get different totals in Revenue column.

in some columns i get export data where it says NULL instead of zero. So I was using Replace feature to convert all NULLs to 0 and making them as whole numbers. I was converting accounts to text format. Yet still get errors and distorted total amount.

Did anybody experience this issue?
 
Too abstract, need to see what you are seeing consider sending the file with fictitious data and flagging exactly where the issues are
 
2018-07-20_10-44-20.jpg

here's the error I'm getting. What could be the reason? I had the value in source file
 
Albertan,

Without being able to step into the file and walk through each applied step, I cannot really help, a png is not enough and I understand if it is highly sensitive data. Consider making a copy of the file and if you are working off a table from that same file delete all rows apart from say 5 and change it up however ensure that you are still seeing the errors prior to sending the file. That said one of the other members maybe able to work off a png.

2 final points null will give you an empty cell 0 a number, will an empty cell cause a problem in your model. 2. are you sure that you did not accidently use the letter o instead of the number 0
 
Ed Kelly said:
Albertan,

Without being able to step into the file and walk through each applied step, I cannot really help, a png is not enough and I understand if it is highly sensitive data. Consider making a copy of the file and if you are working off a table from that same file delete all rows apart from say 5 and change it up however ensure that you are still seeing the errors prior to sending the file. That said one of the other members maybe able to work off a png.

2 final points null will give you an empty cell 0 a number, will an empty cell cause a problem in your model. 2. are you sure that you did not accidently use the letter o instead of the number 0
Click to expand...


I have not changed anything. I take the data and import. Now, I tried to format it to "Decimal" or to "123", still get errors. I will try to save file under .xlsx from .xls and then do Replace values "NULL" through CNRL H to 0 and format cells to General.
 
albertan said:
I have not changed anything. I take the data and import. Now, I tried to format it to "Decimal" or to "123", still get errors. I will try to save file under .xlsx from .xls and then do Replace values "NULL" through CNRL H to 0 and format cells to General.
Click to expand...

figured out a solution by modifying a source data. can't do it while transforming the data
 
albertan said:
figured out a solution by modifying a source data. can't do it while transforming the data
Click to expand...

Perfect Albertan, well done. Sometimes merely by talking through a problem you get to the answer yourself!
 
You must log in or register to reply here.
Back
Top