cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lgpower
Helper II
Helper II

Dataflow decimal columns stored as whole numbers in CDS

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 🙂

 

1 ACCEPTED SOLUTION

Accepted Solutions

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 '.'

View solution in original post

4 REPLIES 4
lgpower
Helper II
Helper II

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. 

 

 

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.. 

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 '.'

View solution in original post

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. 

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,480)