Help with powerquery to extract JSON parameter values from the data

Question

Help with powerquery to extract JSON parameter values from the data

Hellothere8028 821

Hi All,

Hope you are doing well!...I am trying to split the different parameter values for just the correct ,added and removed codes at a vbillid level..In the attached excel file the column codemap has the json from which the codes (belonging to removed, revised or added need to be extracted) from the different coding parameter categories such as cpt drg, modifiers, secondaryDiagnosis, principalDiagnosis, secondaryProcedure, observationhours , dischargeStatus ,principalprocedure,hcpcs

Attached the excel file with the input and the output data sheets...Can you please help me here...

Below is how my JSON data looks like

{"cpt": {"added": [], "correct": [{"code": "99497", "description": "ADVANCE CARE PLANNING FIRST 30 MIN"}, {"code": "96160", "description": "ADMINISTRATION OF PATIENT-FOCUSED HEALTH RISK ASSESSMENT INSTRUMENT (EG, HEALTH HAZARD APPRAISAL) WITH SCORING AND DOCUMENTATION, PER STANDARDIZED INSTRUMENT"}], "removed": [], "revised": []}, "drg": {"correct": [], "revised": []}, "poa": {"correct": [], "revised": []}, "hcpcs": {"added": [{"code": "G0402", "description": "INITIAL PREVENTIVE EXAM"}], "correct": [], "removed": [], "revised": []}, "modifiers": {"added": [{"code": "25", "description": "Significant Separately Identifiable E&M Service by the Same Physician on the Same Day"}], "correct": [], "removed": [], "revised": []}, "dischargeStatus": {"correct": [], "revised": []}, "observationHours": {"correct": [], "revised": []}, "injectionInfusion": {"added": [], "correct": [], "removed": [], "revised": []}, "principalDiagnosis": {"correct": [{"code": "Z0000", "description": "ENCNTR FOR GENERAL ADULT MEDICAL EXAM W/O ABNORMAL FINDINGS (ICD-10) "}], "revised": []}, "principalProcedure": {"added": [], "correct": [], "removed": [], "revised": []}, "secondaryDiagnosis": {"added": [{"code": "E1165", "description": "TYPE 2 DIABETES MELLITUS WITH HYPERGLYCEMIA (ICD-10) "}, {"code": "Z136", "description": "ENCOUNTER FOR SCREENING FOR CARDIOVASCULAR DISORDERS (ICD-10) "}, {"code": "Z1211", "description": "ENCOUNTER FOR SCREENING FOR MALIGNANT NEOPLASM OF COLON (ICD-10) "}], "correct": [{"code": "M25512", "description": "PAIN IN LEFT SHOULDER (ICD-10) "}, {"code": "Z7984", "description": "LONG TERM (CURRENT) USE OF ORAL HYPOGLYCEMIC DRUGS (ICD-10) "}, {"code": "Z791", "description": "LONG TERM (CURRENT) USE OF NON-STEROIDAL NON-INFLAM (NSAID) (ICD-10) "}, {"code": "Z0000", "description": "ENCNTR FOR GENERAL ADULT MEDICAL EXAM W/O ABNORMAL FINDINGS (ICD-10) "}, {"code": "M25512", "description": "PAIN IN LEFT SHOULDER (ICD-10) "}, {"code": "Z7984", "description": "LONG TERM (CURRENT) USE OF ORAL HYPOGLYCEMIC DRUGS (ICD-10) "}, {"code": "Z791", "description": "LONG TERM (CURRENT) USE OF NON-STEROIDAL NON-INFLAM (NSAID) (ICD-10) "}, {"code": "Z1339", "description": "ENCNTR SCREEN EXAM FOR OTHER MENTAL HLTH AND BEHAVRL DISORD (ICD-10) "}], "removed": [], "revised": [{"to": {"code": "M25512", "description": "PAIN IN LEFT SHOULDER (ICD-10) "}, "from": {"code": "Z79899", "description": "OTHER LONG TERM (CURRENT) DRUG THERAPY (ICD-10) "}}]}, "secondaryProcedure": {"added": [], "correct": [], "removed": [], "revised": []}, "facilityEvaluationManagement": {"added": [], "correct": [], "removed": [], "revised": []}, "professionalEvaluationManagement": {"added": [], "correct": [], "removed": [], "revised": [{"to": {"code": "99213", "description": "OFF/OP VIS, EST PT, 2KEY COMP: EXPAND PROB HX; EXPAN"}, "from": {"code": "99396", "description": "PERI COMP PREV MED E&M W/HX/EXAM, EST PT;40-64YR"}}]}}

Also below the way my Input and output tables looks like

Can you please help me here!..

Thanks,
Arun

Accepted answer

4 additional answers

Your answer

Answer 1

Here is the code example to parse your JSON:

DECLARE @json nvarchar(max) = N'
{
   "cpt":{
      "added":[],
      "correct":[
         {
            "code":"99497",
            "description":"ADVANCE CARE PLANNING FIRST 30 MIN"
         },
         {
            "code":"96160",
            "description":"ADMINISTRATION OF PATIENT-FOCUSED HEALTH RISK ASSESSMENT INSTRUMENT (EG, HEALTH HAZARD APPRAISAL) WITH SCORING AND DOCUMENTATION, PER STANDARDIZED INSTRUMENT"
         }
      ],
      "removed":[],
      "revised":[]
   },
   "drg":{
      "correct":[],
      "revised":[]
   },
   "poa":{
      "correct":[],
      "revised":[]
   },
   "hcpcs":{
      "added":[
         {
            "code":"G0402",
            "description":"INITIAL PREVENTIVE EXAM"
         }
      ],
      "correct":[],
      "removed":[],
      "revised":[]
   },
   "modifiers":{
      "added":[
         {
            "code":"25",
            "description":"Significant Separately Identifiable E&M Service by the Same Physician on the Same Day"
         }
      ],
      "correct":[],
      "removed":[],
      "revised":[]
   },
   "dischargeStatus":{
      "correct":[],
      "revised":[]
   },
   "observationHours":{
      "correct":[],
      "revised":[]
   },
   "injectionInfusion":{
      "added":[],
      "correct":[],
      "removed":[],
      "revised":[]
   },
   "principalDiagnosis":{
      "correct":[
         {
            "code":"Z0000",
            "description":"ENCNTR FOR GENERAL ADULT MEDICAL EXAM W/O ABNORMAL FINDINGS (ICD-10) "
         }
      ],
      "revised":[]
   },
   "principalProcedure":{
      "added":[],
      "correct":[],
      "removed":[],
      "revised":[]
   },
   "secondaryDiagnosis":{
      "added":[
         {
            "code":"E1165",
            "description":"TYPE 2 DIABETES MELLITUS WITH HYPERGLYCEMIA (ICD-10) "
         },
         {
            "code":"Z136",
            "description":"ENCOUNTER FOR SCREENING FOR CARDIOVASCULAR DISORDERS (ICD-10) "
         },
         {
            "code":"Z1211",
            "description":"ENCOUNTER FOR SCREENING FOR MALIGNANT NEOPLASM OF COLON (ICD-10) "
         }
      ],
      "correct":[
         {
            "code":"M25512",
            "description":"PAIN IN LEFT SHOULDER (ICD-10) "
         },
         {
            "code":"Z7984",
            "description":"LONG TERM (CURRENT) USE OF ORAL HYPOGLYCEMIC DRUGS (ICD-10) "
         },
         {
            "code":"Z791",
            "description":"LONG TERM (CURRENT) USE OF NON-STEROIDAL NON-INFLAM (NSAID) (ICD-10) "
         },
         {
            "code":"Z0000",
            "description":"ENCNTR FOR GENERAL ADULT MEDICAL EXAM W/O ABNORMAL FINDINGS (ICD-10) "
         },
         {
            "code":"M25512",
            "description":"PAIN IN LEFT SHOULDER (ICD-10) "
         },
         {
            "code":"Z7984",
            "description":"LONG TERM (CURRENT) USE OF ORAL HYPOGLYCEMIC DRUGS (ICD-10) "
         },
         {
            "code":"Z791",
            "description":"LONG TERM (CURRENT) USE OF NON-STEROIDAL NON-INFLAM (NSAID) (ICD-10) "
         },
         {
            "code":"Z1339",
            "description":"ENCNTR SCREEN EXAM FOR OTHER MENTAL HLTH AND BEHAVRL DISORD (ICD-10) "
         }
      ],
      "removed":[],
      "revised":[
         {
            "to":{
               "code":"M25512",
               "description":"PAIN IN LEFT SHOULDER (ICD-10) "
            },
            "from":{
               "code":"Z79899",
               "description":"OTHER LONG TERM (CURRENT) DRUG THERAPY (ICD-10) "
            }
         }
      ]
   },
   "secondaryProcedure":{
      "added":[],
      "correct":[],
      "removed":[],
      "revised":[]
   },
   "facilityEvaluationManagement":{
      "added":[],
      "correct":[],
      "removed":[],
      "revised":[]
   },
   "professionalEvaluationManagement":{
      "added":[],
      "correct":[],
      "removed":[],
      "revised":[
         {
            "to":{
               "code":"99213",
               "description":"OFF/OP VIS, EST PT, 2KEY COMP: EXPAND PROB HX; EXPAN"
            },
            "from":{
               "code":"99396",
               "description":"PERI COMP PREV MED E&M W/HX/EXAM, EST PT;40-64YR"
            }
         }
      ]
   }
}
';

DECLARE @Categories TABLE (
    RowId int IDENTITY(1, 1),
    Category nvarchar(100),
    ValueInJSON nvarchar(max),
    ObjectType smallint
);

INSERT INTO @Categories
SELECT *
FROM OPENJSON(@json);

DECLARE @rowID int = 1;
DECLARE @maxRowID int = (SELECT MAX(RowID) FROM @Categories);
DECLARE @category nvarchar(100);
-- Output table
DECLARE @OutputTable TABLE (
    ErrorParameter nvarchar(100),
    Type_Of_Correction nvarchar(20),
    Coded_Value nvarchar(20),
    New_Value nvarchar(20)
);

WHILE (@rowID <= @maxRowID)
BEGIN
    SELECT @category = Category FROM @Categories WHERE RowID = @rowID;

    INSERT INTO @OutputTable(ErrorParameter, Type_Of_Correction, Coded_Value, New_Value)
    SELECT @category AS ErrorParametr, 'added' AS Type_Of_Correction, '' AS Coded_Value, code AS New_Value
    FROM OPENJSON(@json, '$.' + @category + '.added')
    WITH (
        code varchar(10) '$.code'
    )
    UNION
    SELECT @category AS ErrorParametr, 'correct' AS Type_Of_Correction, '' AS Coded_Value, code AS New_Value
    FROM OPENJSON(@json, '$.' + @category + '.correct')
    WITH (
        code varchar(10) '$.code'
    )
    UNION
    SELECT @category AS ErrorParametr, 'removed' AS Type_Of_Correction, '' AS Coded_Value, code AS New_Value
    FROM OPENJSON(@json, '$.' + @category + '.removed')
    WITH (
        code varchar(10) '$.code'
    )
    UNION
    SELECT @category AS ErrorParametr, 'revised' AS Type_Of_Correction, from_code AS Coded_Value, to_code AS New_Value
    FROM OPENJSON(@json, '$.' + @category + '.revised')
    WITH (
        from_code varchar(10) '$.from.code',
        to_code varchar(10) '$.to.code'
    );

    SET @rowID = @rowID + 1;
END

SELECT * FROM @OutputTable;

Hellothere8028 821 Reputation points

2021-07-19T09:54:56.053+00:00

tThank you so much @Guoxiong ...Really appreciate your help!

Answer 2

Hellothere8028 821

@Guoxiong : Really appreciate your help!..But I am also looking for some help with powerquery for the above json data..

Answer 3

Herbert Seidenberg 1,191

Excel 365 Pro Plus with Power Pivot and Power Query.
With EasyDataTransform for json.
https://www.mediafire.com/file/88e80bm42naw070/06_23_21.xlsx/file
https://www.mediafire.com/file/ig9iunz3kqkwiv0/06_23_21.pdf/file

Hellothere8028 821 Reputation points

2021-07-19T09:55:57.943+00:00

Thank you so much @Herbert Seidenberg ...Really appreciate your help!
Hellothere8028 821 Reputation points

2021-07-19T09:56:15.627+00:00

Can you please let me know how to use this..
Lz._ 9,016 Reputation points

2021-07-19T10:03:34.45+00:00

@Hellothere8028

How can someone reply you? None of the 2 people who suggested you something is targeted in your last comment. In other words to whom was your message?

Try again...
Hellothere8028 821 Reputation points

2021-07-19T10:12:11.063+00:00

@Herbert Seidenberg : Can you please let me know how to use this..

Sorry Lz-3068...I have posted a new comment...
Lz._ 9,016 Reputation points

2021-07-19T10:35:30.327+00:00

@Hellothere8028

I will let Herb. answer but things are clear to me. He used - as he wrote - EasyDataTransform to parse the JSON you shared as text only (and not as attachement) to this thread. If you look at the note on cell B4 in the file I shared that's also stated

Answer 4

Lz._ 9,016

@Hellothere8028

The Json representation you posted as text is parsed with Power Query in this file to download

Hellothere8028 821 Reputation points

2021-07-19T12:38:56.18+00:00

Thank you so much @Lz._
Lz._ 9,016 Reputation points

2021-07-19T12:50:46.587+00:00

@Hellothere8028 . You're welcome Man

Next time please upload & share a file. There's always a risk of error with a source a text copied/pasted from a web page

And as this seems to help please UpVote my post (can help others) given you already accepted as answer the one from GuoxiongYuan-7218

Thanks

Answer 5

Lz._ 9,016

@Hellothere8028

Just uploaded a better version here

Share via

Help with powerquery to extract JSON parameter values from the data

4 additional answers

Your answer