topic Re: JSON Record with Duplicate Column Names: DataFormat.Error: Duplicate name 'value'. in Power Query

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

Anonymous — Mon, 15 Mar 2021 15:50:48 GMT

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

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

edhans — Mon, 15 Mar 2021 15:42:14 GMT

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.

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

Anonymous — Mon, 15 Mar 2021 15:56:30 GMT

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?

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

edhans — Mon, 15 Mar 2021 17:36:52 GMT

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

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

Anonymous — Tue, 07 Feb 2023 16:08:13 GMT

Hello, did you solve this?

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

Thanks