topic Re: Removing zeros from after decimal point in DAX Commands and Tips

Removing zeros from after decimal point

EF — Mon, 11 Nov 2019 16:04:03 GMT

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?

Re: Removing zeros from after decimal point

Greg_Deckler — Mon, 11 Nov 2019 16:20:20 GMT

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"

Re: Removing zeros from after decimal point

EF — Mon, 11 Nov 2019 16:41:35 GMT

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.

Re: Removing zeros from after decimal point

Greg_Deckler — Mon, 11 Nov 2019 16:52:03 GMT

Do you mean back to original source data?

Re: Removing zeros from after decimal point

EF — Mon, 11 Nov 2019 17:20:50 GMT

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)

Re: Removing zeros from after decimal point

Greg_Deckler — Mon, 11 Nov 2019 17:32:12 GMT

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?

Re: Removing zeros from after decimal point

EF — Mon, 11 Nov 2019 18:20:22 GMT

Source data is SQL.

Re: Removing zeros from after decimal point

Greg_Deckler — Mon, 11 Nov 2019 19:41:25 GMT

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.

Re: Removing zeros from after decimal point

EF — Mon, 11 Nov 2019 19:45:30 GMT

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.

Re: Removing zeros from after decimal point

Greg_Deckler — Mon, 11 Nov 2019 19:54:16 GMT

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.

Re: Removing zeros from after decimal point

EF — Mon, 11 Nov 2019 20:20:17 GMT

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.

Re: Removing zeros from after decimal point

Greg_Deckler — Tue, 12 Nov 2019 04:44:42 GMT

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 🙂

Re: Removing zeros from after decimal point

EF — Tue, 12 Nov 2019 15:27:48 GMT

Thank you so much Greg!!

Worked perfectly!

Re: Removing zeros from after decimal point

mike_brooks — Tue, 11 Oct 2022 19:56:52 GMT

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

Re: Removing zeros from after decimal point

Anonymous — Fri, 25 Aug 2023 07:34:52 GMT

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.

Re: Removing zeros from after decimal point

Daniel_PowerBI — Thu, 29 Feb 2024 16:12:57 GMT

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

Thank you mike_brooks