Json檔案未通過電源查詢加載

我有一個需要決議的 json 檔案，但我必須在 power query 評估檔案之前將特定列從數字轉換為文本。我必須這樣做，因為這個數字格式的特定列會導致 power query 錯誤地評估某些記錄。

我能夠從堆疊溢位的某個人那里獲得以下列出的代碼，作為有關如何完成此轉換的建議。

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

uj5u.com熱心網友回復：

這似乎有效（更新的正則運算式）在以 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('"&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{_}
),
newJson=Json.Document(Text.Combine(p,"#(lf)"))
in newJson

標籤：json 擅长 正则表达式 电源查询

上一篇：從Python中的一列字串中提取連續的大寫單詞

下一篇：進制轉換，十進制 轉換為2進制，再由二進制轉換為16進制，截取8位二進制組合字串