UPDATE 1/22/2024: The following post describes a basic pattern for calling the BigTime API with Power Query. standupanalytics.com has since released a Power BI App which handles all of the complicated API interactions and provides a really great environment for analyzing Big Time data.

---

Big Time is a SaaS product for time & expense entry, billing, project management, etc. This guide will show how to connect to the data with Power Query.

A common theme I find with SaaS products is that the “reporting” features are too rigid and lack a degree of tweak-ability, or they are only somewhat valuable because the data can’t be included with reports from your other systems or SaaS products.

This is where Excel or Power BI can help. Just connect to the API, it’s that easy! Or is it? The built in data connectors really are that simple, but Power Query can connect to just about any API even if there is no built in option for it. It takes some manual coding to make it work though. If you need Big Time data, but you’ve never crafted your own GET and POST http requests in Power Query then this info is for you.

The Big Time API documentation has all the technical details about how to connect. It involves sending your username (email address) and password to get a token. That token is then used to make subsequent requests for data. This process will be handled by the below code. Simply enter this into the advanced editor and add your username and password in the respective lines at the top. This code creates a function that can be invoked over the various endpoints.

As with any API, there are endpoints which when queried, will respond with the requested data. The Big Time documentation will be helpful here in describing the data available at each endpoint.

(Endpoint as text) =>
  let
    username = "ENTER HERE",
    password = "ENTER HERE",
    url = "https://iq.bigtime.net/BigtimeData/api/v2",
    FormData = "{UserId: '" & username & "', Pwd: '" & password & "'}",
    Post = Json.Document(
      Web.Contents(
        url,
        [
          Headers      = [#"Content-Type" = "application/json"],
          RelativePath = "/session",
          Content      = Text.ToBinary(FormData)
        ]
      )
    ),
    Token = Post[token],
    FirmId = Post[firm],
    Get = Json.Document(
      Web.Contents(
        url,
        [Headers = [#"X-auth-token" = Token, #"X-auth-realm" = FirmId], RelativePath = Endpoint]
      )
    )
  in
    Get

When you invoke the function for the first time, you will have to set the credentials which in this case will be “Anonymous”.

As you see in the below picture, I have named the function GetBigTimeData and invoked it with the endpoint /picklist/projects. It returns a list of records with the data. This list format is important because the endpoints may respond with data containing different columns so each list will need to be converted to tables and expanded separately. But all the hard work of querying the API is done by the reusable function, just invoke it each time you need it.

You will likely run into the situation where the API responds with a list of IDs, but doesn’t give much useful information beyond that (for example, Task ID).

You will need to invoke the function over each of the task IDs. This post from Mynda Treacy has some good tips on how to accomplish that.

---

Check out standupanalytics.com if you need a more comprehensive and instant implementation solution.