Forum Discussion
Help with Formula/ Power query to get the number of matching row counts of codes
Hi All,
Hope you are doing well!... I have the input data at a ctextid and a vbillid level...I also have the list of codes input by a human (agentcode) and the number of codes input by an automated algorithm (predicted code)... Now for all the individual codes in the agent code column , I need to find the count of rows in the agentcode column that have the specific code and the count of rows in the predict code column that have matching codes for the specific set of ctextid and vbillid ....Please find attached with the input tab and the needed output tab..Can you please help me here..
Thanks,
Arun
11 Replies
One more PQ variant is in attached file
- Hi Sergei, Really appreciate your response...The only problem with this is that the percentages are going beyond 100 when the number of times the code is present in predictedcode is greater than the number of times the code is present in the agentcode...So the logic that I am looking to build is that for a unique code in the agentcode (Number of encounters that have the matching code in predictcode for the agent code)/ total number of encounters with code in the agent code column.. An encounter is a combination of ctext id and visitbillid... For example consider a agentcode C102 for an encounter (ctextid:3421 vbillid : 231) if the same code is present in predictcode for the same encounter(ctextid:3421 vbillid : 231 then the matching count is 1 ..(Numerator -> Number of matching counts for the code/ Denominator -> Number of times the code appears in the agentcode column)..Basically I am trying to calculate the precision for a particular agent code
Could you please generate sample file for testing of such behaviour. In current one I see nothing > 100% with current PQ logic as well as no agentcode C102 and ctextid:3421 you mentioned.
Sorry, I was able to attach the excel file.
It´s weird, if I have it in my desktop it showed me an error but now it was inside a folder Downloads and it works.
- Hi alannavarro, Really appreciate your response...The only problem with this is that the percentages are going beyond 100 when the number of times the code is present in predictedcode is greater than the number of times the code is present in the agentcode...So the logic that I am looking to build is that for a unique code in the agentcode (Number of encounters that have the matching code in predictcode for the agent code)/ total number of encounters with code in the agent code column.. An encounter is a combination of ctext id and visitbillid... For example consider a agentcode C102 for an encounter (ctextid:3421 vbillid : 231) if the same code is present in predictcode for the same encounter(ctextid:3421 vbillid : 231 then the matching count is 1 ..(Numerator -> Number of matching counts for the code/ Denominator -> Number of times the code appears in the agentcode column)..Basically I am trying to calculate the precision for a particular agent code
I did it with power query, but for some reason it doesn´t allow me to attach an excel file.
I can copy and paste the steps in the advance editor if it works for you or if you have an email I can send you the file.
