Use Power BI to view your Smartthings devices

Intro

This is more of a proof of concept to show that you can read from custom and not-often-used data sources into Power BI. As you go through this small showcase/tutorial, I encourage you to think about what type of data would benefit from you being able to look at it in a cohesive dashboard.

Use Case

I have many IoT devices in my home and mainly use Smartthings to manage them. Smartthings does not provide a friendly web interface and instead relies on their phone app. This is not a big deal, but we will use this an excuse to pull the data into Power BI.

The Smartthings API

Smartthings provides a REST API. We will use this to pull the data into Power BI. To access the API, you will need to create a token. You do that by going here: https://account.smartthings.com/tokens

Click on Generate Token and select access to read devices. Write down your token as you will not be able to retrieve it later.

The authorization type is Bearer, we will dwell into that later. These are the two endpoints we will be using:

• https://api.smartthings.com/v1/devices
• https://api.smartthings.com/v1/devices/{deviceId}/status

You can read more on them here: https://smartthings.developer.samsung.com/docs/api-ref/st-api.html

Power BI Datasets

We will be designing this in such a way that we will be using two datasets. One for each endpoint.

Dataset #1: Device List

The first dataset will bring in a list of devices in our smartthings account. Open a new PowerBI window and select Get Data -> Web.

Next, Select Advanced

Enter the following information:
URL parts: https://api.smartthings.com/v1/devices
HTTP request header parameters: Authorization: Bearer <your_token>

Click OK, then Connect

A new query will open. Right click and select Into table.

Then click on List

Right click the header and select To Table, then click Ok

Expand the columns by clicking the icon in the header and click OK

You now have a list of all your devices. Click Close & Apply

Iterating over devices

The previous step lays the foundation for the next which is getting the status of all the devices programmatically.

Create a new blank query from Get Data -> Blank Query

In this query we will write a function that takes the device ID as a parameter. Go into the advance editor and put in the following code (mind your authorization token).

(device as text) =>
let
Source = Json.Document(Web.Contents("https://api.smartthings.com/v1/devices/"& device &"/status", [Headers=[Authorization="Bearer ad6fcfdd-a47b-d3ab-9b7e-asdsdsd01b137"]])),
#"Converted to Table" = Record.ToTable(Source)
in
#"Converted to Table"

Click on Done

Dataset #2: Device Status

Create a new dataset in the advanced editor such that it references the first dataset, then chooses the deviceId from it and subsequently calls the function we just created.

let
Source = devices,
newTable = Table.SelectColumns(Source,{"Column1.deviceId"}),
#"Invoked Custom Function" = Table.AddColumn(newTable, "Device", each Query1([Column1.deviceId])),
#"Expanded Device" = Table.ExpandTableColumn(#"Invoked Custom Function", "Device", {"Name", "Value"}, {"Device.Name", "Device.Value"}),
    #"Expanded Device.Value" = Table.ExpandRecordColumn(#"Expanded Device", "Device.Value", {"main"}, {"Device.Value.main"}),
    #"Expanded Device.Value.main" = Table.ExpandRecordColumn(#"Expanded Device.Value", "Device.Value.main", {"actuator", "sensor", "switch", "configuration", "healthCheck", "switchLevel", "refresh", "polling"}, {"Device.Value.main.actuator", "Device.Value.main.sensor", "Device.Value.main.switch", "Device.Value.main.configuration", "Device.Value.main.healthCheck", "Device.Value.main.switchLevel", "Device.Value.main.refresh", "Device.Value.main.polling"}),
    #"Expanded Device.Value.main.switch" = Table.ExpandRecordColumn(#"Expanded Device.Value.main", "Device.Value.main.switch", {"switch"}, {"Device.Value.main.switch.switch"}),
    #"Expanded Device.Value.main.switchLevel" = Table.ExpandRecordColumn(#"Expanded Device.Value.main.switch", "Device.Value.main.switchLevel", {"level"}, {"Device.Value.main.switchLevel.level"}),
    #"Expanded Device.Value.main.switchLevel.level" = Table.ExpandRecordColumn(#"Expanded Device.Value.main.switchLevel", "Device.Value.main.switchLevel.level", {"value", "unit", "timestamp"}, {"Device.Value.main.switchLevel.level.value", "Device.Value.main.switchLevel.level.unit", "Device.Value.main.switchLevel.level.timestamp"}),
    #"Expanded Device.Value.main.switch.switch" = Table.ExpandRecordColumn(#"Expanded Device.Value.main.switchLevel.level", "Device.Value.main.switch.switch", {"value", "timestamp"}, {"Device.Value.main.switch.switch.value", "Device.Value.main.switch.switch.timestamp"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Device.Value.main.switch.switch",{{"Device.Value.main.switch.switch.value", "Status"}, {"Device.Value.main.switchLevel.level.timestamp", "Last Check"}})
in
    #"Renamed Columns"

You will get a message about the firewall. You can turn this off by going to Options -> Privacy -> Ignore Privacy Level settings.

Click Close & Apply

You can now use the datasets to create your own Smartthings dashboard. I personally used it to check the status and the dimming levels of all my smartlights.

Conclusion

This was a fun project that uses Power BI to integrate into a rest api. Do you have other uses or ideas? How about creating a power app to alter the status and control the devices? Have questions or comments? Please reach out. I would love to hear your ideas.