Viewing data in Microsoft BI

In this guide we'll look at how to import your data into Microsoft Power BI  which can further increase your insight of data stored in Tadabase. 

To start, make sure you download Power BI (Download Link) and follow the steps to install and activate. 

Please note: This guide is an introduction on how to get your data into Power BI. Tadabase's API will return a paginated response, as seen in our developer documentation and the query below. For information on how to build data models, manage connected records, and paginating through a REST API response - Tadabase is unable to provide support on these topics.

In your Power BI project, under Get Data select Blank Query

Once the Power Query Editor opens up, right-click on the new query to open up the Advanced Editor.

Next, copy and paste the code below and be sure to replace the 4 values with your own API credentials and table ID. If you're not sure how to access that, please see our API support doc and developer documentation.

let
     apiUrl = "https://api.tadabase.io/api/v1/data-tables/<REPLACE-WITH-YOUR-TABLE-ID>/records?limit=100&page=1",
     options = [Headers =[
          #"X-Tadabase-App-Key"= "<REPALCE WITH YOUR APP KEY>", 
          #"X-Tadabase-App-Secret"= "<REPLACE WITH YOUR APP SECRET >",
          #"X-Tadabase-App-id"="<REPLACE WITH YOUR APP ID>"
          ]
          ],
    Records = Web.Contents(apiUrl , options)  
in
    #"Records"

The script above will only load the first 100 records. To paginate through all the records  you can try the script below instead: 

let 
    apiUrl = "https://api.tadabase.io/api/v1/data-tables/<REPLACE-WITH-YOUR-TABLE-ID>/records",
    perPage = 100,
    GetJson = (Url) =>
        let Options = [Headers =[
    			          #"X-Tadabase-App-Key"= "<REPALCE WITH YOUR APP KEY>", 
                          #"X-Tadabase-App-Secret"= "<REPLACE WITH YOUR APP SECRET >",
                          #"X-Tadabase-App-id"="<REPLACE WITH YOUR APP ID>"
			          ]
		          ],
            RawData = Web.Contents(Url, Options),
            Json    = Json.Document(RawData)
        in  Json,

     GetPage = (Index) =>
        let Page  = "page=" & Text.From(Index),
            Limit   = "limit=" & Text.From(perPage),
            Url   = apiUrl & "?" & Page & "&" & Limit,
            Json  = GetJson(Url),
            Value = Json
        in  Value,

     getRecords = (page as number, AccumData as list) =>
            let
                Data = Function.InvokeAfter(()=>GetPage(page), #duration(0,0,0,2)),         
                Result =      
                    if page >= Data[total_pages] 
                    then Table.FromRecords(List.Combine({AccumData, Data[items]}))
                    else @getRecords(page + 1, List.Combine({AccumData, Data[items]})) 
            in
                Result,
 
    Records  = getRecords(1, {})
in
    Records

Click Done and double click on the newly created button in the query editor.

Next click on List link: 

Since the list is in JSON it will show up as single rows, we therefore need to expand it into a table be clicking To Table

In the following window, press OK to accept the default settings

Now we must expand the each record into columns, you can do that by clicking the expand button: 

Rename the columns from the slug (ie. field_58) to whatever name makes more sense to you. 

Finally, click on Close & Apply. 

You'll now be able to use your data in your Tadabase app to create dynamic and powerful reports in Power Bi