Excel - Data Cleaning for Analysis - Remove Duplicate Numerical Values in a single cell

hello Anonymous 

please check if this accomodate your need.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZBLCoAwDETv0rXgNEltepbi/a9h/LS1utCFCIWZPGh4JGcHP9ojELvBJQDqrQgTp5rzkJ0N+yxxrQ2liSf/BkW5o/CMEikXBDpkpciSFdbgUbN9VWW5aHyNqJpxb7Y5SzClmqcr2ox+WTl4vx/hDzQv", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date of Service" = _t, #"Patient Case Number (Unique Value)" = _t, #"CPT Code" = _t, #"HCPS Code" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date of Service", type date}, {"Patient Case Number (Unique Value)", Int64.Type}, {"CPT Code", Int64.Type}, {"HCPS Code", Int64.Type}}),
#"Filled Down" = Table.FillDown(#"Changed Type",{"Date of Service", "Patient Case Number (Unique Value)", "HCPS Code"}),
#"Removed Duplicates" = Table.Distinct(#"Filled Down", {"CPT Code"})
in
#"Removed Duplicates"

since you are using merge cell in excel, those merge cell will be blank valu so you need to fill down those blank value.

after that, just remove duplicate on what column you want to be removed.

the other way is using DISTINCT or DISTINCTCOUNT in your DAX, then it will only count a unique value.

Hope this will help.

Thank you.

Hi Anonymous ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If the responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you!!