Decimal Values Precision

[FrancoisHuard]

How can a change the precision of a decimal value

Let says I have the following table

CREATE TABLE dbo.Test

(Column1 NUMERIC(32,12))

INSERT INTO dbo.Test VALUES(12.211112312131),(13.555578711555),(0.000000555551),(0.000100262551)

Then I create the PowerBI and using the table above, then in “Edit Query” add a new column which will be =1/[Column1] , it gives me a decimal with a precision of 6 when I could have 18.

I don’t have the same issue when I create a table in PowerBI directly and add the same values, my new column for row 1 has 1/12.211112312131 = 0.081892621608807947 as opposed to 0.081892 when I the table from the database.

I want to have more decimal precision at the  "edit query" level and not the "report design level". Anyone has an idea of how to achieve this.

Thank You

[Greg_Deckler]

Hmm, the M language specification states that the precision for a Number should be 15-16 digits.

"A number is represented with at least the precision of a Double (but may retain more precision). The Double representation is congruent with the IEEE 64-bit double precision standard for binary floating point arithmetic defined in [IEEE 754-2008]. (The Double representation have an approximate dynamic range from 5.0 × 10−324 to 1.7 × 10308 with a precision of 15-16 digits.)"

However, I am seeing what you are basically, when the Change Type happens I get 8 decimal places. I do get one 5.55551E-07.

@ImkeF- Do you have any wizardry for this?

[ImkeF]

Haven't had an issue like this before. Only thing I can imagine is to buffer the table after import before doing further transformation.

But this of course would prevent query folding, so will load the whole selection so far to the model.

[Greg_Deckler]

Hmm, buffering didn't seem to work, I am trying with this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSMzIEAiNjQyNDY0OlWB2gmLGeKRCYW5gbGgJpsJiBngEYgCRMDRFChgYGRmZGYKFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Decimal = _t]),
    Buffer = Table.Buffer(Source),
    #"Changed Type" = Table.TransformColumnTypes(Buffer,{{"Decimal", type number}})
in
    #"Changed Type"

[ImkeF]

Now I see what you mean. Oddly enough, when that table is loaded to the datamodel, the missing decimals show up again. So it might just be a display issue in the query editor.

[Greg_Deckler]

Ah, that's interesting. I wonder if someone from Microsoft can confirm that it is simply a display issue. I wonder if there is some calculation that could be performed to prove out whether or not it is maintaing the precision behind the scenes? 

[FrancoisHuard]

I don't think it is only display.

If you bring your data to the Report Pane, you don't see the remaining decimal even if I add precison from the formatting button.

I also tried to add "total" to my column and sum it. The result (sum) of the column show that the data was rounded at 6 of precision. 

[ImkeF]

formatting Gregs example as Decimal - Decimal - with 15 digits

Returns all of them in the table view:

So if your SQL-data doesn't show the same behaviour, I'd still suggest to try the Table.Buffer first.

[FrancoisHuard]

The problem is not loading the data in the model, it has more to do with caclulation in the Query Editor

See my example below, 1 column is data from the table straight into the data model, 2nd column is calculation (1/value from table) made in the query editor and 3rd column is calculation (1/value from table) made in the reporting pane.  

Report PaneQuery Editor

[ImkeF]

You have to change the type of the new calculated column to number in the query editor, otherwise it wouldn't work! (and format Decimal-Decimal-15 in data model).

Pls check this file, where everything works as it should: https://1drv.ms/u/s!Av_aAl3fXRbehbFUfBxB7xLJMuLZSw

[FrancoisHuard]

But you do what is stated in my original post "I don’t have the same issue when I create a table in PowerBI directly and add the same values, my new column for row 1 has 1/12.211112312131 = 0.081892621608807947 as opposed to 0.081892 when I the table from the database."

I am not importing data into Power BI, I'm using direct query. I ommited the word "query" in my original post, sorry it would have made it clearer that I am in Direct Query mode.

Changing the format into the query editor did not change the precision.

By the way, thank you very much for you help solving this.

[ImkeF]

Indeed.

You might check if there is an issue filed on it already, otherwise open a new bug-report.