Connecting the Kahua data store API to Power BI

Connecting data store API to Power BI

This guide shows the user how to connect the Kahua data store API endpoint to a Power BI file in order to load and refresh data.

Setting up Postman

1. Log in to Postman - create a new collection and add a new request
2. Click on the Authorization tab of your collection
3. Choose OAuth 2.0 and navigate to Kahua to obtain your client ID and client secret (More information about Kahua API authentication Kahua API Authentication)
4. Enter in that information and click on Get New Access Token at bottom of the screen
5. Create your bearer token and save it to your clipboard

Connecting the data store API as a data source in Power BI

1. Open a new file in Power BI

2. Click on Transform Data to open the Power Query Editor

3. Click on Manage Parameters and add two parameters: one for the Host URL (named HostURL) and one for the bearer token (named BearerToken)

   

4. Click on New Source and choose Blank Query.

5. Click on the Advanced Editor.

6. Copy and paste the following:

   Copy

   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16
   17
   18
   19
   20
   21
   22
   23
   24
   25
   26
   27
   28
   29
   30
   31
   32
   33
   34
   35
   36
   37
   38
   39
   40
   41
   42
   43
   44
   45
   46
   47
   48
   (DataTable as text) =>
   let
       PageSize = 1000,
   
       // Function to fetch a single page
       GetPage = (Offset as number) =>
           let
               Response = Json.Document(
                   Web.Contents(HostURL,
                       [
                           RelativePath = "api/v1/datastore/table/" & DataTable,
                           Query = [
                               offset = Text.From(Offset),
                               limit = Text.From(PageSize)
                           ],
                           Headers = [
                               #"Authorization" = "Bearer " & BearerToken,
                               #"Accept" = "application/json"
                           ]
                       ]
                   )
               ),
               Records = try Response[records] otherwise {}
           in
               Records,
   
       // Function to get all pages
       GetAllPages = List.Generate(
           () => [Offset = 0, Data = GetPage(0)],
           each List.Count([Data]) > 0,
           each [
               Offset = [Offset] + PageSize,
               Data = GetPage([Offset] + PageSize)
           ],
           each [Data]
       ),
   
       // Flatten all record lists into one
       AllRecords = List.Combine(GetAllPages),
   
       // Convert to table
       Table = if List.Count(AllRecords) > 0 then
           Table.FromRecords(AllRecords)
       else
           #table({}, {})
   in
       Table
   

It should look like the following



7. Click on your new query (shown as a function) and enter in the app_name/entity_name. Information about where to find app and entity names can be found here

8. Click Invoke to create your new query
9. Rename your new data table

Troubleshooting Tips

1. Attempt to run your API call in Postman and see which HTTP Status call appears. More information can be found here

2. If your query runs successfully, but there are no records in your query, click on New Source and choose the Web Option. Click on Advanced and enter in your Host URL, Endpoint, and Bearer Token in the format shown below.