Solved: Removing zeros from after decimal point - Page 2

EF · ‎11-11-2019

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 the end (which are basically irrelevant) so they don't match the related dataset.

An easy solution would be to convert to number (since that removes ending zeros) but the codes can include letters so they cannot be converted.

Example:

If I have F41.1, F41.10, F41.100, F90.0, F90, 310, 310.0, 310.00, 309.4, 309.40, 309.41

I want F41.1, F90, 310, 309.4, 309.41

Not sure if better in DAX or Power Query.

Any ideas?

Greg_Deckler · ‎11-11-2019

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 🙂

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

Removing zeros from after decimal point

Helpful resources

Microsoft Fabric Learn Together

Power BI Monthly Update - April 2024

Fabric Community Update - April 2024