Forum Discussion

EF's avatar
EF
Helper II
6 years ago
Solved

Removing 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 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's avatar
    Greg_Deckler
    6 years ago
    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 🙂

15 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    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's avatar
      EF
      Helper II
      6 years ago

      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.