Forum Discussion
EF
Helper II
Helper IIRemoving zeros from after decimal point
Hi all, I have a dataset that includes diagnosis codes, and a related dataset that acts as a key to categorize/explain each diagnosis. Problem is that some diagnoses were entered with zeros at th...
- In Query Editor select your query that loads your table
- Select your column, Diagnosis. choose Transform | Split Column | By Delimiter in ribbon
- Make sure delimiter is a period (.)
- OK
- 2 columns are created, should be Diagnosis.1 and Diagnosis.2
- Right click Diagnosis.2 and choose Replace Values
- Value to find 0
- Replace with leave blank
- Select Advanced Options, make sure Match entire cell contents is NOT selected
- OK
- Zeros are gone
- Choose Add Column in ribbon and then Custom Column
- Use this formula: if [Code.2] = null or [Code.2] = "" then [Code.1] else [Code.1] & "." & [Code.2]
- Remove Diagnosis.1 and Diagnosis.2
- Rename your new column Diagnosis
- Give Greg a Kudo 🙂
You can customize the format of your data by using custom numeric format characters.
This is what I used to remove trailing 0's from my decimal number.
https://learn.microsoft.com/en-us/dax/format-function-dax
Daniel_PowerBI
Advocate I
Advocate IMaybe it did not solve EF 's problem but it sure did solve mine!!!
Thank you mike_brooks