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.
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.
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):
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.
Here is an example for sending a single string parameter:
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.