Forum Discussion
Decimal Values Precision
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
11 Replies
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?
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.
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"
