Forum Discussion

michaeldurieux's avatar
michaeldurieux
Helper I
9 years ago

Build a report two web queries from API REST JSON

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...
Need Help
v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

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's avatar
michaeldurieux
Helper I
9 years ago

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's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    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