none
Get and Transform Errors when importing data RRS feed

  • Question

  • I have a export file from Oracle that I'd like to bring to Excel through Get & Transform. When the export is done, I get the data with numbers but also with characthers such as NULL. When I bring the data into Excel I get some errors. It says  that data cannot convert to numbers. I also get other errors but when I click on error it brings me to a data but I don't see any error. 

    When I compare the total after I bring in the data, I get a different total. I think because of errors, I get some values missing. 

    Did anybody experience this? It is sooo annoying


    albertan

    Friday, July 20, 2018 4:51 AM

Answers

  • At this moment, before doing Get and Transform, I go to source data and I'm changing NULL to zero and formatting cells to number with 2 decimals. It is working that way.

    albertan

    Sunday, July 29, 2018 7:11 PM

All replies

  • Hi albertan

    Couple of months ago I helped someone having a VLOOKUP issue within Excel. Finally figured out the issue was due to the dataset. Dataset was an export from Oracle E-Business Suite. In that export an offending invisible char was present on some records. Whatever I tried on the Excel side (formula/VBA) I couldn't remove that char. User did not have the hand on the Business solution to get a "better" dataset so workaround had to be implemented.

    Interestingly enough that special char was also invisible after saving a couple of "invalid" records as CSV. Opening the CSV with ie Notepad did not allow "seeing" the offending char although it was there.

    I'm not saying this is the root cause of your problem, but it could… Hope this helps

    Friday, July 20, 2018 8:44 AM
  • Hi Albertan

    Quite unbelievable you ask the question. The reason is provided at the bottom of your picture. PQ cannot convert the value - to a Number. Easy to reproduce:

    As you do not provide any information regarding your source data format how can one tell you why? Here is an indication… To replicate the issue I exported a basic XL file where the Values were formatted as Accounting numbers (with that formatting a 0 value is displayed as -) as CSV. Consequently the CSV is as follow

    Product;Value
    A; 100.30  
    B; 100.15  
    C; -    
    D; -    

    and logically, in PQ, when I ask to tranform the Value column (that is TEXT format when opening the CSV) as Decimal Numbers, the values that can't be converted to a Number… => Above error

    • Proposed as answer by anthony34 Sunday, July 22, 2018 8:09 PM
    Saturday, July 21, 2018 8:15 AM
  • I was asking what could be done to avoid this error. At this moment, I'm going to a source data and formatting all numbers to "General" and substituting "NULL" with 0 numbers. 

    The case is closed.


    albertan

    Saturday, July 21, 2018 4:35 PM
  • You can try using a native SQL-statement when importing your data from Oracle instead where you delete the NULL-values.

    When you say that your figures don't sum up correctly, the reasons for the errors you see might have a different nature: SOME might come from the NULL or "-" or whatever and some might come from faulty separators in your number columns. So another method would be to do the replacements before the "Change Type"-step. When you first replace NULL, then change type and then still have errors, you know that these are the ones with different problems. Then sometimes replacing "." with "," or whatever the reason is, helps.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Sunday, July 29, 2018 8:05 AM
    Moderator
  • At this moment, before doing Get and Transform, I go to source data and I'm changing NULL to zero and formatting cells to number with 2 decimals. It is working that way.

    albertan

    Sunday, July 29, 2018 7:11 PM