JSON Record with Duplicate Column Names: DataFormat.Error: Duplicate name 'value'.

[Anonymous]

Hello,

I am trying to call an API via a custom function in PowerBI. It works well for most of the API calls in the report, however I am encountering issue with the final table I need to create.

I am calling an API from our monitoring system PRTG, this is called twice prior, to get the "Device ID's" and the "Sensor ID's" - the Sensor ID table is where I invoke the custom function which I've included below, filling the ID parameter with the ID column in Sensor ID;

(id as text, sdate as text, edate as text) =>

let
    Source   = Json.Document(Web.Contents("https://PRTGSERVERNAME.REDACTED.com/",[RelativePath="api/historicdata.json?id=&avg=84600&sdate=&edate=&username=&passhash=",Query=[id=id,sdate=sdate,edate=edate,username="REDACTED",passhash="REDACTED"]])),
sensors = Source[sensors]
 in 
    Source

When this runs, all of the cells created by the function are "Error", with the error code "An error occurred in the ‘’ query. DataFormat.Error: Duplicate name 'value'."

I've run the API call in postman, and think I understand the problem, just not how to solve it - the JSON that is returned (see below) has multiple columns named "value"

Is there some way I can rename all of the columns (or some of them at least) so that the query passes, and I can then transform them further from there? I cannot expand them as a result of the error resulting from the column names. 

{
    "prtg-version": "20.3.60.1623",
    "treesize": 28,
    "histdata": [
        {
            "datetime": "1/31/2021 11:30:00 PM - 11:00:00 PM",
            "datetime_raw": 44228.9583333333,
            "value": "20 msec",
            "value_raw": 20.4038,
            "value": "16 msec",
            "value_raw": 16.1791,
            "value": "28 msec",
            "value_raw": 27.8521,
            "value": "<1 %",
            "value_raw": 0.0363,
            "value": "0 %",
            "value_raw": 0.0000,
            "coverage": "100 %",
            "coverage_raw": 10000
        },
    ]
}

[Anonymous]

Hello, did you solve this?

I am in the exact same situation and It's painful.

Thanks

[edhans]

I don't see a way to do this. It seems the format is not valid JSON. Even when I manually remove the duplicate names, the next error is this:

So at best that file seems to be "json-ish" but not a true JSON file. Perhaps someone more versed in JSON schemas can chime in, but it seems you'll have to go back to the source system and get it fixed.

[Anonymous]

Hi @edhans 

Thank you for a quick response, much appreciated.

I should have thought to troubleshoot by adding it as a json file - my bad! I think the JSON snippet I sent originally was missing the second square bracket - I've added that in now to my original post!

If I remove the duplicate column names it works for me (now that I've included the closing ] ). Is there any way I can address the column problem in Power Query by forcing it to rename or anything along those lines? 

[edhans]

The issue @Anonymous is with a JSON, all of this section comes in as one record, and each field has a name:

So in Power Query it looks like this:

Power Query cannot even open it to parse it into a record with the duplicate Value/Value_Raw fields. So I don't think your JSON is following standards. Those either need to be renamed in the JSON, or created as separate records within the main record. JSON Structures | JSON tutorial | w3resource