Power BI

Configuration

• Open Power BI and click "Get data from other sources"

• In the dialog that appears, type "blank" into the search bar, then select "Blank Query" and click "Continue"

• The Query Editor will appear with Query1 created for you. Right click Query1and select "Advanced Editor". This will open the code interface.

• Delete whatever default value appears and paste the following code into the box

  • NOTE: this code includes a default query and filter that should work for most users if you use the datatable.

let
    makeFilter = (filter as record) as text =>
        let
            val = Text.FromBinary(Json.FromValue(filter))
        in
            val,
    FetchData = (options as record, optional records as nullable list) as list =>
        let
            CurrentSource = Json.Document(Web.Contents(#"cloud", options)),
            records = if records <> null then records & CurrentSource[records] else CurrentSource[records],
            ScrollId = try CurrentSource[scroll_id] otherwise null,
            Check = if ScrollId <> null
                then @FetchData([
                    RelativePath = "/api/v1/tables/" & #"workgroupId" & "/" & #"table" & "/search/scroll",
                    Query = [ scroll_id = ScrollId ],
                    Headers=[Authorization="Bearer "&#"API token"] 
                ], records)
                else records
        in
            Check
in
    FetchData([
        // do not cache
        IsRetry = true,
        RelativePath = "/api/v1/tables/" & #"workgroupId" & "/" & #"table" & "/search",
        Query = [
            rows = "10000",
            sort = "-event_time",
            timeframe = "6 hours ago",
            filters = {
                makeFilter([ field = "cpu_usage", operator = ">", value = "10" ])
            }
        ],
        Headers=[Authorization="Bearer "&#"API token"]
    ])

• Click "Done". An error should appear telling us that we need to provide the missing parameters

Expression.Error: The name 'cloud' wasn't recognized

• To start declaring the parameters, click "Manage Parameters". This will open a dialog.

  • Click "New"

  • Enter the parameter name. It must match the name provided here (or in the error) exactly (the values are case-sensitive)

  • Enter the value specific to your use case

• The required parameters are as follows

Parameter	Description	Suggested Values
cloud	The URL of the cloud you want to connect to	https://app.ardexa.com https://eur1.ardexa.com
workgroupId	The ID of the workgroup you wish to extract data from	You can find the workgroup ID in Admin -> Workgroup
table	The table you wish to extract data from	data, solar, wind10m
API token	Secret credentials used to authenticate with the API	API Tokens

• Once all the parameters are populated, the error "Please specify how to connect" will appear. Click "Edit credentials"

• Select "Anonymous" and click "Connect". NOTE: The connection is not actually anonymous, we simply provide the credentials as a parameter so that it can be used in the script.

• This will return a List of Records which is the reply from the API

• Import the data into Power BI

Modifying the query

To modify the query, we need to look at the Query object:

Query = [
    rows = "10000",
    scroll = "true",
    sort = "-event_time",
    timeframe = "6 hours ago",
    filters = {
        makeFilter([ field = "cpu_usage", operator = ">", value = "10" ])
    }
],

If you have already read the API Quick Start Guide, this should look very familiar. If you haven't read that article, please read it first and then return here.

TL;DR

• Don't touch rows

• Add filters using the makeFilter()function following the pattern shown in the example

Other suggestions

• Once you've finished building your query, add the scroll = "true" parameter. This will enable you to import more than the default cap of 10,000 records. You can potentially import millions of records if your computer can handle it.

• makeFilterwill convert any object you provide into JSON text

• timeframe and sort , in fact all the parameters, work exactly like they do in the API Search Functions