Excel Power查询编号到文本

Question

我正在尝试解析JSON文件，但是该文件是以一种奇怪的方式设置的，Excel正在遇到问题。请参阅下面的代码示例。

"in_network":
[
        {
            "negotiation_arrangement": "ffs",
            "name": "CT Scan",
            "billing_code_type": "X",
            "billing_code_type_version": "2022",
            "billing_code": "XXXX",
            "description": "CT Scan",
            "negotiated_rates": [
                {
                    "provider_references": [
                        380.39
                    ],
                    "negotiated_prices": [
                        {
                            "negotiated_type": "percentage",
                            "negotiated_rate": 4.00,
                            "expiration_date": "9999-99-99",
                            "service_code": [
                                "99"
                            ],
                            "billing_class": "Ultra",
                            "billing_code_modifier": []
                        }
                    ]
                },
                {
                    "provider_references": [
                        380.6
                    ],
                    "negotiated_prices": [
                        {
                            "negotiated_type": "percentage",
                            "negotiated_rate": 2.00,
                            "expiration_date": "9999-99-99",
                            "service_code": [
                                "99"
                            ],
                            "billing_class": "Ultra",
                            "billing_code_modifier": []
                        }
                    ]
                }
]

有一个名为Provider_references的字段，该电源查询是读取为数字数据类型的。该字段有一些行，条目380.6和一些行，条目为380.60。 Excel将380.6和380.60行读取为380.6。 。我试图将Power查询编辑器中的数据类型更改为文本，认为这可以解决问题，但是Excel仍然无法区分这两个条目，并继续将380.6和380.60 Group_ids汇总在一起。有没有办法确保在解析JSON文件时捕获Provider_References字段有任何略有差异？

进行

的	6	个
2.00	百分比	。
JSON	2.00	百分比
380.6	是	这
应对	​	​

方式

解析	文件	​
​	​	​
​	​	​
​	​	​
​	​	​

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

Excel无法区分这两个条目，因为JSON并未将项目引用以使其成为标签，并且将其评估为

我无法获得该样本以适当评估有效JSON的数字。假设您可以，这可能对您有用。它在下面的行中添加了引用和尾随的引号[包含 provider_references 的行]

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