在电源查询中的JSON文件中的数字添加引号

Question

我有一个需要解析的JSON文件，但是在Power查询评估文件之前，我必须将特定的列从数字转换为文本。我必须这样做，因为此特定列的数字格式正在导致电源查询以错误地评估某些记录。

我能够从Stack Overflow上的某人那里获得以下列出的代码，以建议如何进行此转换。

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

但是，当我运行此代码时，JSON文件永远不会加载到Excel。它只是保持运行和运行。我猜它被困在一个无限的循环中，但是由于我不熟悉电源查询，所以我无法调试。任何建议都很棒。谢谢

请参阅下面的JSON文件的结构

{
    "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"
                            ]
                        }
                    ]
                }
            ]
        }
        
    ]
}

Answer 1

从380开始的任何数字上的引号，转换为字符串

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('"®ex&"','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{_}
),
newJson=Json.Document(Text.Combine(p,"#(lf)"))
in newJson