Help with Power Query to split JSON coded data

[chandramouliarun]

Hi All,

Hope you are doing well...I am trying to split the column of data containing JSON formatted data...I am trying to extract the following columns -complexity, the codes with type ICD-10-CM separated by columns and the clinicalencounterid.. Please find attached the file with the input and the expected output columns...Can you please help me here...




Thanks,
Arun

[JohnTopley]

Function JSON(r As Range, opt As Integer) As String
Dim a() As String, xstr As String

xstr = r.Value
xstr = Replace(xstr, """", "")
xstr = Replace(xstr, "\", "")
xstr = Replace(xstr, "{", "")
xstr = Replace(xstr, "}", "")
xstr = Replace(xstr, "[", "")
xstr = Replace(xstr, ",", ":")

a = Split(xstr, ":")
Select Case opt

Case Is = 1

    For i = 0 To UBound(a)
        If Trim(a(i)) = "complexity" Then
            JSON = Trim(a(i + 1))
            Exit Function
        End If
    Next i

Case Is = 2

    xstr = ""
    For i = 0 To UBound(a)
        If Trim(a(i)) = "name" Then xstr = xstr & Trim(a(i + 1)) & ","
    Next i

    JSON = Trim(Left(xstr, Len(xstr) - 1))
    Exit Function

Case Is = 3

    For i = 0 To UBound(a)
    If Trim(a(i)) = "clinicalEncounterId" Then
            JSON = Trim(a(i + 1))
            Exit Function
        End If
    Next i

End Select

End Function

in OUPUT

in B2

=json(Input!$B2,COLUMNS($B$1:B$1))

Copy across and down

As you have 365, I am sure there will be a formula solution,

[chandramouliarun]

@ John Topley...Thanks !...This works...Really appreciate your help!