Sharing: Example Power BI Power Query to execute Flux query

InfluxDB 2
,
1

(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

Notes:

  • 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.
3 Likes
2

Hi,

I tried the above method but it keeps throwing me an error.

Web.Contents with the Content option is only supported when connecting anonymously.

Here is the M language code from PowerBI:

let
body = Text.ToBinary("
‘from(bucket:"“test2"”)
|> range(start: -12h)
|> filter(fn: (r) => r._measurement == ““temperature””)
|> aggregateWindow(every: 1h, fn: mean)’
“),
url = “http://20.xx.xx.xx:8086”,
options = [
Headers = [
Authorization=” Token pcSpeRixxxxxxxxxxxxxxxxxxxxxxxxxxpN1QQ2oQTuQ==",
Accept=“application/json”,
Content=“application/json”],
RelativePath=“api/v2/query”,

Query=[org=“xxxxx”],
Content=body
],
result = Web.Contents(url, options)

in
result

3

@raginigupta6 You must select Anonymous authentication for the web data source in the Data Source Settings. I can no longer update my original post because of its age. Please see the link in this comment for more.

1 Like
4

Thank you very much for sharing this M code. Have it up and working. Super appreciate your help.