Hub IoT API REST to PowerBI Service

[Barrera]

I want to display in a Power BI report some information from Azure IoT Hub: the DeviceID and Status (Disconnected/Connected) of the devices at that moment

print 1

Initially, I managed to do this in Power BI Desktop using a Web Connector as the data source.
The connector uses the IoT Hub REST API with the following code:

let
    url = "https://****.azure-devices.net/devices?api-version=2020-03-13",
    token = "SharedAccessSignature sr=poc-refit.azure-devices.net&sig=******&se=2072193716&skn=iothubowner",
    Source = Json.Document(Web.Contents(
            "https://****.azure-devices.net",
            [
                RelativePath = "devices",
                Query = [#"api-version" = "2020-03-13"],
                Headers = [Authorization = token]

            ]
        )),
    Table = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Expanded = Table.ExpandRecordColumn(Table, "Column1", {"deviceId","connectionState"}, {"DeviceId","Status"})
in
    Expanded

This works fine — every time I hit refresh, the latest information appears in the report visual.

However, when I publish the report to the workspace and open it online in Power BI Service, the report no longer has access to updated information.

I noticed that the semantic model throws errors like "Due to consecutive errors, scheduled refresh has been disabled. Resolve the error in the dataset and enable it again."

Does anyone know how to display this information in the published report? It could be near real-time updates, refreshed every 15 minutes, for example — that would already meet the requirement.

[v-saisrao-msft]

Hi @Barrera,

Checking in to see if your issue has been resolved. let us know if you still need any assistance.

Thank you.

[v-saisrao-msft · Accepted solution]

Hi @Barrera,

Use by placing an Azure Function between Power BI and the IoT Hub REST API. The Function calls IoT Hub using a SAS token stored in its configuration, then secures itself with Azure AD so Power BI can access it using OAuth2 via the Web connector. This creates a fully supported, secure endpoint that Power BI can refresh reliably on a schedule.

Thank you.

[v-saisrao-msft]

HI @Barrera,

Have you had a chance to review the solution we shared by @lbendlin? If the issue persists, feel free to reply so we can help further.

Thank you.

[lbendlin]

Consider pushing rows into a Power BI streaming dataset or a Fabric eventhouse instead, and then use a dashboard to get your real time data displayed automatically as it comes in.

[Barrera]

Power BI streaming dataset will deprecated so I decide to discard it

Using Fabric Eventhouse would involve another module that I’m not familiar with, so I haven’t tried it yet. Besides, I believe it would require setting up a database and managing a table to track the latest status of each device (Connected/Disconnected) based on the events Eventhouse receives after being created.

The problem is that this table would be blind to devices that only generated events before the new database was created. On the other hand, sending the request directly to IoT Hub already provides the status of all devices currently registered there.

The request I showed works perfectly in Power BI Desktop the way I need it. A simple cURL command to IoT Hub returns a JSON with all the information I need.
But in Power BI Service, this approach falls short.

[lbendlin]

two comments

1. "The announcement of my death was premature" - Streaming Hybrid Dataset.  (Deprecation and sunset pushed out from 2024 to 2027)

2. Have a look at LogStash  ...