cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AndyB
Helper I
Helper I

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

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

 

 

3 REPLIES 3
edhans
Super User
Super User

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:

edhans_0-1615822828388.png

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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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? 

 

jsonexample.png

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

edhans_1-1615829642194.png

So in Power Query it looks like this:

edhans_0-1615829595615.png

 

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

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors