Unanswered

Dataflow decimal columns stored as whole numbers in CDS

(0) Share

Report

Posted on by Community Power Pla...

Hi ,

Can't figure out what I am doing wrong. I use dataflows to get a.o. decimal type columns into CDS entities using Odbc.Query(source,sql )

- I get the desired output from our SQL server

- Columns are of decimal type in power query interface (also tried switching these around --> convert to decimal another time / convert to string --> convert back to decimal)

- Entity fields are decimal type in CDS

When checking the data in CDS, app forms, labels, the numbers are there but decimals are missing.

1000,58 is stored as 100058
1000,5 is stored as 10005

and so on.

Does this ring a bell for anyone?

Thanks in advance 🙂

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Community Power Pla... on at

Like (0)

Report

Solved

In the power query editor I set the decimal data type along with Dutch language settings. After that committed the output in new entity.

New entity was created with floating decimal type and the numbers are stored correctly.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Ok so I THOUGHT this was solved, but it isn't.

Now that the datatype is floating decimal the numbers are stored as folllows:

1000,58 is stored as 100058,00
1000,5 is stored as 10005,00

Any help would still be appreciated..

Was this reply helpful? Yes No
Verified answer

David Jennaway 716 on at

Like (0)

Report

I think the problem is probably to do with the format of the numbers. I expect something is treating the , as a thousands separator (which is ignored), rather than denoting the decimal place. I suspect this is somewhere due to language settings.
The issue may be with the connector to SQL; one thing you could try is changing the format to use a . as the thousands separator, wither through language settings, or converting to string and replacing ',' with '.'

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hi @DavidJennaway,

You got it!

Changed system notation to en-US
In power query changed project options to en-US
In formatting options changed to "en-US"

This (or at least one of these actions) got it to run.

Not sure how to manage keeping my system notation on Dutch though..

This will work for now 🙂 .

Thanks a lot.

Was this reply helpful? Yes No