Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Rotcorp
Regular Visitor

How to replace decimal values but retain the original whole number

I'm working with a netsuit timesheeting export that records a half fraction as .3

e.g. 37.5 hours a week shows as 37.3.
I have tried useing a conditional column to convert the .3 to a .5 but if the text contains .3 (else return the column value) however I can't figure out how to return the value before the decimal point

Rotcorp_0-1683290424342.png

 

Am I using the correct method?

 

thanks in advance

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @Rotcorp 

use the "Replace Value" option then manually edit thecode adding a conditional if statement and that says something like

each if Text.Contains ( [Column], ".3" ) then ".3" else null

View solution in original post

3 REPLIES 3
mrcss23
Advocate IV
Advocate IV

Hi. My suggestion is

1. Create a column that round down the value (column1) : Number.RoundDown([Total Hours])

2. Create a column2 that returns the decimal part, i.e..  [Column1] - [Total Hours] 

3. Create column3 a which is [Column1]+0.5

4. Then make a conditional column 

IF Column2 =0.7 than Column3

ELSE

[Total Hours]

tamerj1
Super User
Super User

Hi @Rotcorp 

use the "Replace Value" option then manually edit thecode adding a conditional if statement and that says something like

each if Text.Contains ( [Column], ".3" ) then ".3" else null

Thank you that worked.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors