'Excel Power Query Number to Text
I'm trying to parse a json file but the file is set up in a strange way that excel is having problems deciphering. Please see code example below.
"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": []
}
]
}
]
There is a field called provider_references, that power query is reading as a number data type. This field has some rows with the entry 380.6 and some rows with an entry of 380.60. Excel is reading both 380.6 and 380.60 lines as 380.6. . I tried to change the data type in Power Query Editor to text, thinking this would solve the problem, but excel is still not able to differentiate between the two entries and continues to lump the 380.6 and 380.60 Group_IDs together. Is there a way to ensure that any slight differences in the provider_references field gets captured when parsing the json file?
This is how the json file should be parsed
| provider_references | Negotiated_type | negotiated_rate |
|---|---|---|
| 380.60 | percentage | 2.00 |
| 380.60 | percentage | 2.00 |
| 380.6 | percentage | 2.00 |
| 380.6 | percentage | 2.00 |
But this is how power query is actually parsing the file
| provider_references | Negotiated_type | negotiated_rate |
|---|---|---|
| 380.6 | percentage | 2.00 |
| 380.6 | percentage | 2.00 |
| 380.6 | percentage | 2.00 |
| 380.6 | percentage | 2.00 |
{
"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"
]
}
]
}
]
}
]
}
Solution 1:[1]
Excel is not able to differentiate between the two entries, because the JSON is not putting the item in quotes to make it a label and it is evaluated as a number
I can't get that sample to evaluate properly as valid JSON. Assuming you can, this probably will work for you. It adds the leading and trailing quotes to the row below [the row that contains 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
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 |