The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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?
Solved! Go to Solution.
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
Maybe it did not solve @EF 's problem but it sure did solve mine!!!
Thank you @mike_brooks
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"
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.
Do you mean back to original source data?
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)
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?
Source data is SQL.
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.
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.
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.
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.
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.
Thank you so much Greg!!
Worked perfectly!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
33 | |
20 | |
16 | |
15 |
User | Count |
---|---|
56 | |
51 | |
36 | |
35 | |
31 |