The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi community,
I build a report with a web datasource (data coming from en API REST JSON).
It is about a device which send data (in a single frame we have battery, temperature, CO2, CH4, humidity...) each 10 minutes so I receive timeseries.
Format of the JSON :
{ "results": [ { "series": [ { "name": "Battery", "columns": [ "time", "deviceid", "value" ], "values": [ [ "2017-06-29T13:23:59Z", "DeviceXXX", 85 ], ] }, { "name": "CH4", "columns": [ "time", "deviceid", "value" ], "values": [ [ "2017-06-29T13:23:59Z", "DeviceXX", 0.19 ], ] }, { "name": "CO2", "columns": [ "time", "deviceid", "value" ], "values": [ [ "2017-06-29T13:23:59Z", "DeviceXX", 2284 ], ...
So Battery is interpreted as a column of "series", same for CO and CH4 etc.
I built my report by creating sub requests (a lot!!) to get the values and finally create a consolidation table of all my column/values using merge function
And the result of my consolidation table is the following
==> My first question is to know if there is an optimized way and if it exists an easier solution ?
==> My second question : I have another URL with the same JSON format and same column name (it is just another deviceID). How can I merge the two queries without to have to recreate all sub queries and merging ?
Thanks for your ideas !
Hi @michaeldurieux,
Whtat's your reource table look like? battery, temperature, CO2, CH4, humidity are column or row values, in same table or different table? I am not specific about JSON data, I am trying to reproduce your scenario, but get the following data, it's uncorrect. Could you please share more details for further analysis?
Thanks,
Angelia
Hi @v-huizhn-msft,
I just receive an URL which generate a JSON.
Please see the generated JSON simplified example that I receive (thru URL)
Since it is timeseries data, CH4, CO2 and so on are interpreted as table. I need to rework them to extract column/value.
I follow this link to create my final dataset (consolidation ta ble which is a merge of all sub request that I have created) : link
My point is to know if it exists a better way to visualise these time serie data using PowerBI rather than trying to rebuild a "relational database"
{ "results": [ { "statement_id": 0, "series": [ { "name": "Battery", "columns": [ "time", "deviceid", "value" ], "values": [ [ "2017-07-03T11:34:54Z", "Sigfox_XXX", 91 ], [ "2017-07-03T11:44:58Z", "Sigfox_XXX", 90 ], [ "2017-07-03T11:55:02Z", "Sigfox_XXX", 90 ], [ "2017-07-03T12:05:06Z", "Sigfox_XXX", 90 ], [ "2017-07-03T12:15:10Z", "Sigfox_XXX", 90 ], [ "2017-07-03T12:25:14Z", "Sigfox_XXX", 90 ] ] }, { "name": "CO2", "columns": [ "time", "deviceid", "value" ], "values": [ [ "2017-07-03T11:34:54Z", "Sigfox_XXX", 598 ], [ "2017-07-03T11:44:58Z", "Sigfox_XXX", 647 ], [ "2017-07-03T11:55:02Z", "Sigfox_XXX", 721 ], [ "2017-07-03T12:05:06Z", "Sigfox_XXX", 696 ], [ "2017-07-03T12:15:10Z", "Sigfox_XXX", 721 ], [ "2017-07-03T12:25:14Z", "Sigfox_XXX", 844 ] ] }, { "name": "Humidity", "columns": [ "time", "deviceid", "value" ], "values": [ [ "2017-07-03T11:34:54Z", "Sigfox_XXX", 41 ], [ "2017-07-03T11:44:58Z", "Sigfox_XXX", 41 ], [ "2017-07-03T11:55:02Z", "Sigfox_XXX", 40 ], [ "2017-07-03T12:05:06Z", "Sigfox_XXX", 40 ], [ "2017-07-03T12:15:10Z", "Sigfox_XXX", 39 ], [ "2017-07-03T12:25:14Z", "Sigfox_XXX", 41 ] ] } ] } ] }
Hi @michaeldurieux,
After research, you need to get the date and expand it in Power Query. They create report using the visual supported by Power BI desktop. it seems there no better to upload the data from JSON file, maybe it can be simplified using Json code, but I am not specific JSON code.
Best Regards,
Angelia
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
93 | |
82 | |
64 | |
58 |
User | Count |
---|---|
248 | |
123 | |
112 | |
79 | |
78 |