This article will guide you through the straightforward process of connecting Joiin to Power BI using our API key.


1. In your Power BI, create a new Blank Query:

2. Copy the content below (adding your API key):

let
    Source = (start as text, end as text, companyList as list) =>

let
    apiKey = "INSERT YOUR API KEY HERE",
    companies = companyList,
    apiUrl = "https://app-api.joiin.co/v1/report/trial-balance",
    startDate = start,
    endDate = end,
    currency = "GBP",
    requestData = [
        startDate = startDate,
        endDate = endDate,
        currency = currency,
        companies = companies
    ],
    response = Web.Contents(apiUrl, [
        Headers = [
            #"Content-Type"="application/json",
            #"x-api-key"= apiKey
        ],
        Content = Json.FromValue(requestData),
        ManualStatusHandling = {200}
    ]),
    jsonParsed = Json.Document(response),


    accountsToTable = Table.FromList(jsonParsed[accounts], Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    expandedAccounts = Table.ExpandRecordColumn(accountsToTable, "Column1", {"displayName", "debit", "credit"}, {"Account Name", "Debit", "Credit"}),

    // After expanding, transform the 'Debit' and 'Credit' columns to extract the first element of each list
    transformedColumns = Table.TransformColumns(expandedAccounts, {
        {"Debit", each _{0}, type text}, 
        {"Credit", each _{0}, type text}
    })
in
    transformedColumns
in
    Source

3. Set the name for the query to JoiinTrialBalance:

4. Verify the name is set correctly by checking the panel on the left:

5. Create another Blank query (as in step one 1), this time with the content below (using your company names)

let
    month1 = Table.AddColumn(JoiinTrialBalance("2024-01", "2024-01", {"Company name 1", "Company name 2"}), "Month", each "2024-01", type text),
    month2 = Table.AddColumn(JoiinTrialBalance("2024-02", "2024-02", {"Company name 1", "Company name 2"}), "Month", each "2024-02", type text),
    combinedData = Table.Combine({month1, month2})
in
  combinedData

When you click Next this should result in:

Make sure you remove any existing scripts and that the query's name is spelled correctly, as per the screenshots above.

If you have any more issues, please send over screenshots, and we will assist you.