Forum Discussion

Maho's avatar
Maho
Frequent Visitor
6 years ago
Solved

How to import values with decimals from database

I have problem to import values (number) with two decimals from oracle database, the decimals sign (,) is not included in the value when I import via ODBC, but if I export to an excel file first and ...
Need Help
  • Maho's avatar
    Maho
    5 years ago

    I finally solved this issue with, I fetch the data from an Oracle database with American setup, so I had to do following steps

    1. Change NLS Language in Regedit to American (see picture), then restart computer

     

     

     

     

     

     

     

     

     

     

    2. Change the Regional settings on the Computer, then restart again.

     

     

     

     

     

     

     

     

    3. Check PowerBI settings to make sure it fetch from windows settings

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    4. Finally restart PowerBI.


    Now I got the result for numeric values for example like this

    1,253.69 ( , = thousand separator and . = decimal separator) this works for me now I get all decimals and the calculation is correct.

     

    I hope this will help anyone who has the same problem, I strugled with this for a while.

sabrinalaurel's avatar
sabrinalaurel
Regular Visitor
5 years ago

The comma vs period is a common issue between data sources.  I had a similar issue (missing the decimal after import). The easiest way I found to fix the format after import is by the following:

In the Power Query Editor,

> Select the target column (verify that it is in decimal number format)

> Select the Transform ribbon

> Select "Standard" in the "Number Column" section

> Select "Divide"

> Enter "100" into the pop-up window "value" field

This divides the imported value by 100 thus moving the decimal two places to the left. 

If you only need to move one decimal place, divide by 10 - three decimal places would be divided by 1000. 

Basically, for ever zero after the one, you are moving the decimal one place.

Hope that helps!

Sabrina

Maho's avatar
Maho
Frequent Visitor
5 years ago

Hi Sabrina,

Thanks' for trying to help me, this is still an issue for me and I have spent so much time to try to solve it.

The problem is that the decimals is removed (Swedish decimal ",") in the number.

In the oracle database, it could look like below table, it is a number field 14 + 2 decimals, sometimes it is decimals and sometimes not, so it is not possible to divide all data with 100.

 

I have also tried other national settings but my problem remain.

Kindly Marianne