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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!