Help with Power Query to extract CPT and ICD codes data

[chandramouliarun]

Hi All,

Hope you are doing well!...I am trying to extract the CPT and the ICD codes data from the coded data column Data which is in JSON format..Please find attached the sheet with the input data and the output required data.. Can you please help me here...


Thanks,
Arun

[Glenn Kennedy]

You ask lots of questions. That's great. However, you are not very good at acknowledging the help you have received. In most of your recent threads, you have not indicated whether or not the solutions worked, or said thanks... or, indeed, said ANYTHING.

You get the expertise of all of us for free. The least you can do is acknowledge that help.

[chandramouliarun]

Hi @Glenn Kennedy...Please accept my apologies!...I have to basically respond to the previous 4 threads..I will do it soon...Previously I have responded and marked thread as solved , thanked people for all the questions.. I got held up with some work...

[Glenn Kennedy]

I make it AT LEAST 6... and now it's AT LEAST 7. I suggest you take some time to attend to this, now... not "soon".

CPT:
=TEXTJOIN(", ",,FILTERXML("<A><B>"&SUBSTITUTE(Input!C2,"\"&CHAR(34),"</B><B>")&"</B></A>","//B[following::*[4]='CPT']"))

ICD-10-CM
=TEXTJOIN(", ",,FILTERXML("<A><B>"&SUBSTITUTE(Input!C2,"\"&CHAR(34),"</B><B>")&"</B></A>","//B[following::*[4]='ICD-10-CM']"))

clinicalEncounterId:
=LOOKUP(10^10,--MID(FILTERXML("<A><B>"&SUBSTITUTE(Input!C2,"\"&CHAR(34),"</B><B>")&"</B></A>","//B[preceding::*[1]='clinicalEncounterId']"),3,ROW($1:$15)))

[AliGW]

Previously I have responded and marked thread as solved.

I have to agree with Glenn. Most of your threads are not marked as solved.

I got held up with some work.

A post with two words - thank you - would take under three seconds to post. Given that we are most likely helping you with your work here, I don't think that's too much to ask, do you?

[chandramouliarun]

Hi @AliGw...Again apologies !..I was validating different use cases for the answers and hence the delay...I will make sure to provide updates soon ...

[chandramouliarun]

@ Glenn Kennedy, This works !..Really appreciates your help!

[Glenn Kennedy]

Thank you... for the feedback here and in regard to the other threads, which you have now started to respond to...