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

[LT_Maverick]

Date of Service	Patient Case Number (Unique Value)	CPT Code	HCPS Code
1/1/2023	900081	43239 43247 96361 96361 96374 96375 96375 99283	43239
2/1/2024	900082	38510 88341 88341 88341 88341 88341 88342	38510
3/1/2024	90083	45385 45380 43239 88305 88305 88305 88305 88305 88305	45385

I am cleaning up some data to figure out what our procedural volume by CPT code is. However, I need to remove any duplicates in the "CPT" column. After that I am not sure how to use it as a unique value to calculate volume by date (month, year, fiscal year). 

I plan to put the volumes in a column graph similar to the one below. I plan to use a "filter" to select different CPT codes. 

[Irwan · Accepted solution]

hello @LT_Maverick 

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.

[v-sathmakuri]

Hi @LT_Maverick ,

I hope this information provided is helpful. Feel free to reach out if you have any further questions or would like to discuss this in more detail. If responses provided answers your question, please accept it as a solution so other community members with similar problems can find a solution faster.

Thank you!!

[v-sathmakuri · Accepted solution]

Hi @LT_Maverick ,

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!!

[v-sathmakuri]

Hi @LT_Maverick ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you!!

[Irwan · Accepted solution]

hello @LT_Maverick 

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.