I recently had a customer who wanted to run services on ThingWorx from Power BI to retrieve existing operational data, and we were a bit stumped on how to pass the API key over in the headers, so I did a bit of Googling and pieced together the solution. It's not quite intuitive on the Power BI side, so I thought it would be helpful to share. If you have any other experience with integrating ThingWorx with Power BI, feel free to add a comment.
Prepare ThingWorx
- Create an Application Key that has Run Time execution access to the services you need.
- Understand the inputs needed for the service you would like. I'll have examples of none, one, an InfoTable, and multiple inputs.
Power BI
Following the following steps in Power BI:
1. In Power BI, create a new blank query
2. On the left, right click on Query1 and go to the Advanced Editor:
3. Replace all of the body content with the following, replacing your API key, appropriate end point, and base URL as needed (this is an example with NO input parameters, I'll follow with examples of other parameters):
let
appKey = "your-application-key-here",
endpoint = "Things/YourThingNameHere/Services/YourServiceNameHere",
baseUrl = "https://YourServerNameHere/Thingworx/",
url = Text.Combine({baseUrl,endpoint}),
body = "",
request = Web.Contents(
url,
[
Headers = [
appKey = appKey,
#"Content-Type" = "application/json",
Accept = "application/json"
],
Content = Text.ToBinary(body)
]
),
Source = Json.Document(request)
in
Source
4. Click "Done", and now you'll have a warning about how to connect. Click the "Edit Credentials" button.
5. Leave it on Anonymous and click "Connect":
6. You should now see the return data coming from ThingWorx.
Note that I had a little trouble with this authentication initially and it saved the wrong method. To clear that out, go to the ribbon bar item "Data source settings" and select the server and clear it out.
Other Examples
Here is an example for sending a single string parameter:
let
appKey = "your-application-key-here",
endpoint = "Things/YourThingNameHere/Services/YourServiceNameHere",
baseUrl = "https://YourServerNameHere/Thingworx/",
url = Text.Combine({baseUrl,endpoint}),
body = "{""InputParameter"": ""InputValue""}",
request = Web.Contents(
url,
[
Headers = [
appKey = appKey,
#"Content-Type" = "application/json",
Accept = "application/json"
],
Content = Text.ToBinary(body)
]
),
Source = Json.Document(request)
in
Source
Here's an example of sending a string and an integer:
let
appKey = "your-application-key-here",
endpoint = "Things/YourThingNameHere/Services/YourServiceNameHere",
baseUrl = "https://YourServerNameHere/Thingworx/",
url = Text.Combine({baseUrl,endpoint}),
body = "{""InputString"": ""Hello, world!"", ""InputNumber"" : 42}",
request = Web.Contents(
url,
[
Headers = [
appKey = appKey,
#"Content-Type" = "application/json",
Accept = "application/json"
],
Content = Text.ToBinary(body)
]
),
Source = Json.Document(request)
in
Source
Here is an example for sending an InfoTable. Note that you must supply the dataShape with fieldDefinitions. If you're using an existing Data Shape, you can get the JSON by using the service GetDataShapeMetadataAsJSON() that is on the data shape.
let
appKey = "your-application-key-here",
endpoint = "Things/YourThingNameHere/Services/YourServiceNameHere",
baseUrl = "https://YourServerNameHere/Thingworx/",
url = Text.Combine({baseUrl,endpoint}),
body = "{""propertyNames"": {
""rows"": [
{ ""name"": ""FirstEntityName"", ""description"": ""The first entity"" },
{ ""name"": ""SecondEntityName"", ""description"": ""The second entity"" }],
""dataShape"": {
""fieldDefinitions"": {
""name"": {
""name"": ""name"",
""aspects"": { ""isPrimaryKey"": true },
""description"": ""Entity name"",
""baseType"": ""STRING"",
""ordinal"": 0
},
""description"": {
""name"": ""description"",
""aspects"": {},
""description"": ""Entity description"",
""baseType"": ""STRING"",
""ordinal"": 0
}
}
}
}}",
request = Web.Contents(
url,
[
Headers = [
appKey = appKey,
#"Content-Type" = "application/json",
Accept = "application/json"
],
Content = Text.ToBinary(body)
]
),
Source = Json.Document(request)
in
Source
If I find any more interesting ways to use Power BI with ThingWorx services, I'll add them on here.
