(So many uses of “power” and “query” follow… for great justice)
We currently use Influx OSS and InfluxCloud v1. Grafana, Chronograf, and Power BI are the main applications we run against InfluxDB.
We’re preparing for an eventual transition to InfluxDB v2. One piece of the migration is updating our Power BI reports to use the InfluxDB v2 API. Unfortunately, Microsoft still does not offer native support for InfluxDB in their Power BI tool. So, raw Power Query code is necessary to submit queries to InfluxDB.
We were recently successful in interacting with InfluxDB v2 using Power BI. Below is a snippet of Power Query code for reference. It submits a Flux query to InfluxCloud v2.
let body = Text.ToBinary(" from(bucket: ""<bucket>"") |> range(start: -1h) |> filter(fn: (r) => …) … "), url = "https://us-west-2-1.aws.cloud2.influxdata.com", options = [ Headers = [ Authorization="Token <InfluxDB v2 Token>", accept="application/csv", #"content-type"="application/vnd.flux"], RelativePath="api/v2/query", Query=[org="<Your Organization>"], Content=body ], result = Web.Contents(url, options) in result
- It’s critical for your bucket token to have READ permissions. Otherwise, the Power Query will fail with confusing errors about authentication.
- Any double quotation marks within your Flux query string must be escaped by doubling them up. Note the quadruple quotation marks around
<bucket>in the example.
- The URL is for InfluxCloud 2. Any InfluxDB instance reachable by your Power BI configuration will work here.
- The InfluxDB v2 API returns annotated CSV (instead of JSON as in v1). Power BI readily works with this and will actually automatically handle the table and column types for you fairly well without a great deal of help.
- The Flux query above is obviously incomplete. You must fill in the
…’s per your own needs to complete the query. That said, do note the opening and closing parens, quotation marks, and commas in and around the Power Query
Text.ToBinary()call. Getting the query string, quotation marks, and Power Query call right can be tedious.
- The Power Query above could certainly be more sophisticated and make use of Power BI’s features to set and lookup parameters (variables) outside the query. This example is a bare bones “Hello World” to get you started in connecting InfluxDB v2 to Power BI as a data source.