Build a report two web queries from API REST JSON

[michaeldurieux]

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 !

[v-huizhn-msft]

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

[michaeldurieux]

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

[v-huizhn-msft]

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