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


1. How to retrieve Trial Balance Data in Power BI


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


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.



2. How to retrieve Custom Report/Layout Data in Power BI


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, reportId as text, companyList as list) =>
let
    apiKey = "ENTER YOUR API KEY HERE",
    companies = companyList,
    apiUrl = "https://app-api.joiin.co/v1/report/custom-report",
    startDate = start,
    endDate = end,
    currency = "GBP",
    requestData = [
        startDate = startDate,
        endDate = endDate,
        currency = currency,
        companies = companies,
        customReportId = reportId
    ],
    response = Web.Contents(apiUrl, [
        Headers = [
            #"Content-Type"="application/json",
            #"x-api-key"= apiKey
        ],
        Content = Json.FromValue(requestData),
        ManualStatusHandling = {200}
    ]),
    json = Json.Document(response),

    // flatten sections -> accounts -> fields
    sections = Table.FromList(json[sections], Splitter.SplitByNothing(), {"Section"}),
    withAccounts = Table.ExpandRecordColumn(sections, "Section", {"accounts"}, {"accounts"}),
    accountsRows = Table.ExpandListColumn(withAccounts, "accounts"),
    expanded = Table.ExpandRecordColumn(accountsRows, "accounts", {"displayName","value"}, {"Account Name","value"}),

    // value is [[ "123.45" ]] -> extract to number
    toNumber = Table.TransformColumns(expanded,
      {{"value", each Number.From(Text.From(List.First(List.First(_)))), type number}}),

    // add Month label from start (e.g., "Jan-25")
    monthTxt = Date.ToText(Date.FromText(start & "-01"), "MMM-yy"),
    withMonth = Table.AddColumn(toNumber, "Month", each monthTxt, type text),

    result = Table.RenameColumns(withMonth, {{"value","Amount"}})
   
in
    result
in
    Source

3. Set the name for the query to JoiinCustomReport

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 and customReportId):


let
    customReportId = "<YOUR CUSTOM REPORT ID>"
    month1 = JoiinCustomReport("2025-01","2025-01",customReportId,{"Company 1","Company 2"}),
    month2 = JoiinCustomReport("2025-02","2025-02",customReportId,{"Company 1","Company 2"}),
    combinedData = Table.Combine({month1, month2})
in
    combinedData

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