Removing zeros from after decimal point

[EF]

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?

[mike_brooks]

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]

Maybe it did not solve @EF 's problem but it sure did solve mine!!!

Thank you @mike_brooks 

[Greg_Deckler]

You could try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjMx1DNUitWBsgyQmFC2pYEenAWmjQ3htB4SC8o0sNQzQbCQBIG2xAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Code = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Code", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true), {"Code.1", "Code.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Code.1", type text}, {"Code.2", type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type1","0","",Replacer.ReplaceText,{"Code.2"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value", "Custom", each if [Code.2] = null or [Code.2] = "" then [Code.1] else [Code.1] & "." & [Code.2]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Code.1", "Code.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Code"}})
in
    #"Renamed Columns"

[EF]

Thanks!

That worked for the sample list I wrote.

(I added it as a custom column)

How do I apply that to my actual dataset? Preferably as a column, not table; transforming each row to the correct diagnosis code.

I can't seem to upload a snapshot.

Table is called Diagnoses, Column is Diagnosis.

[Greg_Deckler]

Do you mean back to original source data?

[EF]

Yes.

Which parts of your code need to be changed to connect it to my source data?

(sorry if this isn't so coherent, I'm pretty new at this)

[Greg_Deckler]

In general you can't use Power BI to updat your source data. The solution for your source data would be dependent on the source format, is it Excel, SQL, Oracle, something else?

[EF]

Source data is SQL.

[Greg_Deckler]

OK, you'll have to get someone that knows SQL better than I to write an equivalent update query if you want to fix your suorce data. Probably need to find a SQL forum for that.

[EF]

I don't think I will be able to fix the source data.

Is there any way to make the change on the power bi end? 

It could be a new column, where if there is a zero past the decimal point the zero should be erased.

[Greg_Deckler]

Right, that was the code that I provided, it essentially created a new column with the correct values. You could use that logic to add the new column in Power Query and then that new column will show up in your data model with the corrected values.

[EF]

Thanks for your patience!

I get that the logic works, just not sure what part of your code to replace to use it.

Would you mind walking me thorugh the code, or even highlighting the parts that need to be replaced with my source? I'm getting errors regarding Type.

The column I'm using is a text column named Diagnosis, from table Diagnoses.

[Greg_Deckler · Accepted solution]

1. In Query Editor select your query that loads your table
2. Select your column, Diagnosis. choose Transform | Split Column | By Delimiter in ribbon
3. Make sure delimiter is a period (.)
4. OK
5. 2 columns are created, should be Diagnosis.1 and Diagnosis.2
6. Right click Diagnosis.2 and choose Replace Values
7. Value to find 0
8. Replace with leave blank
9. Select Advanced Options, make sure Match entire cell contents is NOT selected
10. OK
11. Zeros are gone
12. Choose Add Column in ribbon and then Custom Column
13. Use this formula: if [Code.2] = null or [Code.2] = "" then [Code.1] else [Code.1] & "." & [Code.2]
14. Remove Diagnosis.1 and Diagnosis.2
15. Rename your new column Diagnosis
16. Give Greg a Kudo 🙂

[Digiman14]

Hello Mr. Deckler, and thanks for your informative answer. I tried it and ended up with a new column containing both whole numbers without trailing 0's  and decimals. So far so good. But,  this is a text column. In order to use it in a chart it has to be a "numbers" column. So, it has to be formatted to a Decimal number or a Whole number type column. And we're back to square one.. Or am I wrong? very likely.

[EF]

Thank you so much Greg!!

Worked perfectly!