How to import values with decimals from database

[Maho]

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 then to Power BI, the decimal sign is in correct place.

Using ODBC from Oracle DB:

Export from Oracle to excel and then import to Power BI

Declaration in Database

The correct value should be 879,06, but I don't want to go throught an excelfile, because it is a huge amount of data.
Can someone help me with this issue?

[sabrinalaurel]

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 · Accepted solution]

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.

[Maho]

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

[Anonymous]

decimal is a dot not a ','

a ',' denotes seperation before the decimal. So this is a number with that seperation and a decimal: 896,352.25

I would suggest manipulating the data on import before PBI categorises it (e.g. in the SQL code) or change it in the source data.

Many Thanks

POC

[Maho]

So what is the differens here,  it's no problem to get the value from the database in other tools  Qlikview (BI) and Excel, but when I import to Power Bi it jus remove the "," sign and add it in the end instead, I can not understand why, and it is a huge database with all Costs in this format.

I just want Power BI to fetch the data as is in Table, no conversions.

[vivran22]

@Maho 

Hi,

What I have understood from your query that you need to format the number (123,456.00) instead of looking as (123456.00). If this is the case then I would recommend to format the number using Column/Measure properties in Power BI, instead of formatting it in Power Query. Power Query  is primarily meant for data transformation/editing. The data formatting (how it is going to look in the visuals) is done at Power BI desktop.

Regards,

Vivek

[vivran22]

Hello @Maho 

Did it solve your issue?

If yes, then please mark this question as answered. Answered questions help users in the future who may have the same issue or question quickly find a resolution via search.

Thanks!

Vivek

[Maho]

Hi @vivek
No, it is not solved. I think I need to try another explanation.

Ín the database the value is 879,06 Euro, so around 879 Euro

After import to Power BI the cost is 87906 Euro, correct should be 879,06 Euro

Why does Power BI import remove the ","

[vivran22]

I am not sure about "why the Power Query does that?", but if you want the thousand separator(,) in your visuals, then the method I suggested would definitely help.

Vivek

[Maho]

Hi@vivran22 

It does not have anything with thousand separator to do. 

[Rate]

Hello @Maho ,

Any chance fixing this issue? 

I am also looking for a way in which Power BI can import the commas, rather than changing the commas to dots in the Database.

Thanks!

[Anonymous]

Hi,

As noted earlier, you just need to format the number. if you are able to manipulate the data on its way into PBI then simply add '.00' on the end. That way your number will be a decimal as it comes in which you can then easilly format.

Many Thanks

POC

[Anonymous]

Hi @Maho 

The Column header shows that you have imported a 'Decimal Number' 

I think the issue may be more related to the formatting of data in PBI.  Perhaps you could try with the 'Data' view and 'Column Tools'

Hope this helps Sduffy

[Maho]

Sorry, I can not get this to work, it only add the decimal after the value ( and then it will be 87906,00 instead of 879,06.

value shoud be 879,06 and when I add instead with decimal after  87906,00 and that is faulty.

Is it possible to somehow use a script

= Table.TransformColumnTypes(...