Help With PowerQuery to expand coding data

Hi All,

Hope you are doing well!..I am trying to expand the coding data in json format into the following categories...The input has the Billid and ctextid denoted as a patient visit... The codedby is the name of the first coder who performed the coding.. The worktype tells that if it is a coder record or qa (also known as auditor) record... Now I need to extract the JSON data and split into the following categories based on a set of rules (the following for both coder and auditor)..

1) principalDiagnosis is the first code in the JSON under diagnosisCode
2) SecondaryDiagnosis is the rest of the codes in JSON under diagnosisCode
3)Injectioninfusion is taken under ChargeCode and has to have a range between 96372-96379( meaning all codes 96372,96373,96374,96375,96376,96377,96378,96379) , range between 96365-96371
4) ObervationHours also taken ChargeCode and has to have a range between 99234-99236, 99218-99220,99224-99226,99231-99233
5) professionalEvaluationManagement also taken as chargeCode have a range between 99201-99499
6) The rest of the codes under ChargeCode come under cpt category

The order of extraction of ChargeCode is as follows (Injection infusion and then ObservationHours and then professionalevaluationManagement and then cpt codes)
7) Then it is modifiers ,hcpcsUnits.. - The sheet attached for this question is Sample Sheet-Correction Analysis

I have attached the Excel sheet with the Input and the output required..Can you please help me here...

Also the 2nd Question beiing:I have an input sheet which has the Billid and ctextid which denotes a patient visit and the column auditinfo which has the count of added,removed, correct and revised codes..I need to extract for the following categories principalDiagnosis secondaryDiagnosis injectionInfusion observationHours professionalEvaluationManagement cpt modifiers hcpcsUnits... Can you please help me here...Attached the file with input and output... Also i have added the counts only for the correct ones in the output sheet.... The sheet attached for this question is Sample for extracting counts...




Thanks,
Arun

Please Login or Register  to view this content.

Please Login or Register  to view this content.

@Bo_Ry: Thanks for this...Really Appreciate your help!..For the second question I am trying to expand it to removed as well as revised.. I have tried the following but I am getting the error saying that you had entered 6 arguments instead of 4 arguments... can you please help here..

HTML Code: 

Forum rule 2. Programming code must be enclosed in code tags to improve readability.

Please Login or Register  to view this content.

Please put the

Please Login or Register  to view this content.