Counting the number of Diagnosis codes by context ,Specialty and provider-HelpwithPowerQuery/formula

[Arun Chandramouli]

Hi Team,

Hope you are doing well!... I am trying to count the number of diagnosis codes by context ,Specialty and provider..Can you please help me here..Attached the Input and the output required sheets....

Appreciate your help!



Thanks,
Arun

[#2 · Accepted solution]

@Arun Chandramouli Please see the attached file containing a PQ solution in the Output sheet. I have also noticed some discrepancies, probably caused by the fact that you created the example output manually.

The query is fairly standard, except for one little trick that allows you to split the "Diagnosis code" column based on the maximum number of elements in any given field. I picked it up from a video by @Wyn Hopkins not too long ago. 

https://techcommunity.microsoft.com/t5/excel/be-careful-when-splitting-cells-in-power-query/m-p/1130... 

[#3]

Riny_van_Eekelen : Really appreciate your response!..Thank you very much for your help...

[#4]

Thanks for the reference @Riny_van_Eekelen 

There's actually a simpler way using Split Text to Rows option under Advanced

[#5]

@Wyn Hopkins Will keep that in mind for next time :)

[#6]

Hi Riny,Just one more question: If I want to count a diagnosis code only once per row irrespective of the number of times it appears in a row and the arrive at the count stats ..Can you please help me with that...

[#7]

@Arun Chandramouli Not sure I follow. You mean that if, for instance, E1 in the table has two entries for the same diagnose, you only want to count it once?

[#8]

Hi Riny, Yes I meant the same...At a row level if a diagnosis code is present twice or thrice I want to count it only once...

[#9]

@Arun Chandramouli Okay. Then try the attached version. I just entered an extra code on the first row and it only counts once in the output. Have also simplified the query using Wyn's tip. Also added a Trim step, that will get rid for any trailing or leading spaces in the diagnose column.

[#10]

Thanks a lot Riny_van_Eekelen!..