How to export data from many devices to Power BI ?

Having the data is important but it's not enough, it needs to be used. One of the most convenient ways to quickly have a good understanding of the data is with a great visualisation and this is when a tool like Power PI becomes very useful.

How does it work ?

In Power BI a Dataset needs to be created, there the information will be saved. In general terms 2 values will be saved:

- A measurement (which is the value measured for instance 37°C, 3.5 Volts, etc).

- A timestamp (indicating when the measurement happened, for instance: "2023-05-30T17:54:51.294Z"

A tool is used to receive all the updates from the devices, apply the format needed and then send it to Power BI with an API call.

Part 1 of 3: Preparing everything in Power BI

First go to Power BI, this can easily be done from any Office on-line application.

Then, you will arrive in Power BI.

In Power BI the information received from ALSO IoT platform will need to be kept "somewhere". For this, go to "My Workspace", then "New" and then "Streaming Dataset".

For the New streaming dataset, select source of data "API" and then click on Next

Enter the Dataset name. Then, for the values (the information that will come from ALSO IoT Platform), enter as it's shown on the image below.

Then enable "Historic data analysis" and click on Create. The First Dataset is created !

Now, everything on the PowerBI side is being completed. We just need to make sure that the information is sent to the correct end point (provided by Power BI) in the correct format (provided by Power BI). Here below an example:

Part 2 of 3: Sending the data to Power BI

The most efficient way to to this is using a tool that first collects all the data, then gives it the required format and then sends it to the required endpoint. A tool proposed here is Node-RED.

For our projects, we have our own Node-RED server https://projects.allthingstalk.com/. You can easily install your own Node-RED server on any PC, of course, you will need to make sure that the PC is always UP and Running, connected to the internet, all the documentation can be found here: https://nodered.org/docs/getting-started/. In case a local PC can't be used for this purpose, you can also take a Node-RED Server on-line on places like https://www.stackhero.io/en/ for instance.

Once you have the Node-RED, you will need a flow like the one below:

The easiest way to achieve this is importing the file attached at the end of this article:

Once this is done, just some minor changes are needed:

1. On the MQTT node:

Update the topic to reflect your ground and device:

2. In the function node:

Adjust the format of the message (to match what you defined before) and adjust the URL to have the URL needed for your case.

3. On the http request:

No change is needed !

That's all.

Part 3 of 3: Visualising the data in Power BI

In Power BI, go to My Workspace, go to your dataset and click on Create Report:

In case you see something different on the screen, make sure you select Lineage View. Is the most user friendly way to see everything.

Now select the visual table, select the data needed on that table and that's all !

Feel free to try some other visualisations. This can be done very simple: First select the table and then select the new visualisations. 

Keep trying until you find the visual that you like, then click on Save.

The report will be saved with the given name.

Send new data to the dataset. After the new values have been done, go back to your Power BI report and click on the refresh icon.

In case of any question, feel free to reach us and we'll be happy to assist.