cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

How to use ThingWorx REST API with Microsoft Power BI

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

  1. Create an Application Key that has Run Time execution access to the services you need.
  2. 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

image

 

2. On the left, right click on Query1 and go to the Advanced Editor:

image

 

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.

image

5. Leave it on Anonymous and click "Connect":

image

 

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.  

Comments

Thank you really useful !

Version history
Last update:
‎Mar 23, 2022 04:18 PM
Updated by:
Labels (2)