Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
},
]
}
Hello, did you solve this?
I am in the exact same situation and It's painful.
Thanks
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @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?
The issue @AndyB 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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.