Json file not loading with power query-CodePudding

I have a json file that needs to be parsed but I have to convert a specific column from number to text before power query evaluates the file. I have to do this because this particular column being in a number format is causing power query to evaluate some of the records incorrectly.

I was able to get the below listed code from someone on stack overflow as a suggestion of how this conversion could be done.

let  Source = Lines.FromBinary(File.Contents("C:\temp\a.json"), null, null, 1252),
p=List.Transform(List.Positions(Source), each 
    if _ =0 then Source{_} else 
    if Text.Contains(Text.From(Source{_-1}),"provider_references") then """" & Text.Trim(Text.From(Source{_}))& """"  else Source{_}
),
newJson=Json.Document(Text.Combine(p,"#(lf)"))
in newJson

However, when I run this code the JSON file never loads to excel. It just keeps running and running. I'm guessing it's stuck in an infinite loop but since I'm unfamiliar with power query I'm unable to debug. Any suggestions would be great. Thanks

See structure of json file below

{
    "reporting_entity_name": "test",
    "reporting_entity_type": "testr",
    "last_updated_on": "2022-05-05",
    "version": "1.0.0",
    "provider_references": [
        {
            "provider_group_id": 380.1,
            "provider_groups": [
                {
                    "npi": [
                        9999999999
                    ],
                    "tin": {
                        "type": "ein",
                        "value": "57-999999999"
                    }
                }
            ]
        }
    ],
    "in_network": [
        {
            "negotiation_arrangement": "ffs",
            "name": "test",
            "billing_code_type": "RC",
            "billing_code_type_version": "2022",
            "billing_code": "xxxx",
            "description": "test",
            "negotiated_rates": [
                {
                    "provider_references": [
                        380.61
                    ],
                    "negotiated_prices": [
                        {
                            "negotiated_type": "negotiated",
                            "negotiated_rate": 0.00,
                            "expiration_date": "9999-12-31",
                            "service_code": [
                                "22"
                            ],
                            "billing_class": "institutional",
                            "billing_code_modifier": []
                        }
                    ]
                }
            ]
        },
        {
            "negotiation_arrangement": "ffs",
            "name": "test",
            "billing_code_type": "RC",
            "billing_code_type_version": "2022",
            "billing_code": "zzzz",
            "description": "test",
            "negotiated_rates": [
                {
                    "provider_references": [
                        380.60
                    ],
                    "negotiated_prices": [
                        {
                            "negotiated_type": "negotiated",
                            "negotiated_rate": 105.00,
                            "expiration_date": "9999-12-31",
                            "service_code": [
                                "22"
                            ],
                            "billing_class": "institutional",
                            "billing_code_modifier": ["00"
                            ]
                        }
                    ]
                }
            ]
        }
        
    ]
}

CodePudding user response：

I need a bit of help on this from someone on the regex .. I need a regex that will pick out a number that starts with 380, such as 380 / 380.0 / 380.123

Plop that in in place of the the 380[0-9.] below and this should work fine

let Source = Lines.FromBinary(File.Contents("C:\temp\a.json"), null, null, 1252),
fnRegexExtr=(text,regex)=>Web.Page("<script>var x='"&text&"';var y=new RegExp('"&regex&"','g');var b=x.match(y);document.write(b);</script>")[Data]{0}[Children]{0}[Children]{1}[Text]{0} ,
p=List.Transform(List.Positions(Source), each 
if Text.Contains(Source{_},"380") then 
Text.Replace(Source{_},fnRegexExtr(Source{_},"380[0-9.] "),""""&fnRegexExtr(Source{_},"380[0-9.] ")&"""")
else Source{_}
)
in  p