Skip to main content

Power BI

Power BI enables you to analyse & visualise data as well as create custom reports & interactive dashboards. Follow the steps below to start pulling data from the Komodo API into Power BI.

Power BI can be found here.

Compatibility​

The Komodo API is only compatible with Power BI Desktop, as Power BI Web does not support the Web data source.

Setup​

  1. In Power BI, select Web as a data source.

    Select data source

  2. Enter the URL of the API endpoint you're wanting to pull data from.

    Enter API URL

  3. Select Basic as the authentication method, and enter your Komodo API credentials.

    Enter Basic authentication credentials

  4. You should then see data that has been pulled from the API endpoint. Initially this will be limited to the first page of results - see the Pagination section below for more information.

    Example API data

Pagination​

Any API endpoints returning a list of records are paginated using cursor-based pagination. This means you may have to make multiple requests to get all of the data you're wanting.

By default, the query generated by Power BI will only fetch the first page of results. You can alter the query via the Advanced Editor.

Advanced editor button

Example query​

Below is an example query which will fetch all pages of users. You can use this as a template for fetching other paginated data. Just change the values in the highlighted section based on the API endpoint you're wanting to use.

let
    /* CHANGE THE BELOW VALUES */
    BASE_URL = "https://us.east1.api.komodowellbeing.com/rest/v1/users",
    COLUMNS = {"id", "email", "created_at", "firstname", "lastname", "role"},
    COLUMN_TYPES = {
        {"id", Int64.Type},
        {"email", type text},
        {"created_at", type datetime},
        {"firstname", type text},
        {"lastname", type text},
        {"role", type text}
    },
    /* CHANGE THE ABOVE VALUES */

    // Function for fetching a page with cursor-based pagination
    GetPage = (cursor as nullable text) =>
        let
            // Append the cursor to the URL if we have one
            url = if cursor <> null then BASE_URL & "?cursor=" & cursor else BASE_URL,
            Source = Json.Document(Web.Contents(url)),
            data = Source[data],
            // Cast to text for compatibility with GetPage
            nextCursor = if Source[cursor] <> null then Text.From(Source[cursor]) else null,
            #"Converted to Table" = Table.FromRecords({Source})
        in
            [Data = data, NextCursor = nextCursor],

    allPages = List.Generate(
    // First request with no cursor
    () => GetPage(null),
    // Repeat until we receive a null cursor
    each [NextCursor] <> null,
    // Fetch the next page
    each GetPage([NextCursor]),
    // Return the data only
    each [Data]),

    // Combine all page data into a single list
    allData = List.Combine(allPages),

    // Convert data to a table with types
    #"Converted to Table" = Table.FromList(allData, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", COLUMNS),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1", COLUMN_TYPES)

in
    #"Changed Type"