Updating Existing Reports with New Accounting.bi Data (Profit & Loss, Balance Sheet)

April 3, 2024

This guide will walk you through updating your existing reports that use Accounting.bi's Profit & Loss and Balance Sheet data for Xero. We'll be using Excel's Power Query Editor, a built-in Excel tool, to refresh the data with the latest information.

Here's what you'll need:

  • Your existing report in Excel
  • Your new Bearer Token from Accounting.bi (you can find this in your account settings).

Steps:

  • Open the Report:

Locate the Excel report you want to update with new data.

  • Launch Power Query Editor:

Go to the "Data" tab in Excel.
Click on "Get Data" dropdown and select "Launch Power Query Editor."

  • Locate the Reports Table:

Navigate within the Power Query Editor to find the table containing your AccountingBi report data.

  • Edit the M Code: (This step involves editing code. If you're not comfortable with code, consider contacting Accounting.bi support for assistance.)

Click on the "Advanced Editor" option from the "Home" tab within the Query section. This will open the code editor.

Replace the existing M code (the code that retrieves data) with the new code provided below.

New M Code:

"let
Token = "Bearer " & #"Bearer Token", // Replace "Bearer Token" with your actual token
UrlBalanceSheet = "https://api.accounting.bi/connectors/accounting/xero/balanceSheet",
UrlProfitAndLoss = "https://api.accounting.bi/connectors/accounting/xero/profitAndLoss",

GetAPIData = (token as text, url as text) as record =>
let
Source = Json.Document(Web.Contents(url, [Headers=[Authorization=token]])),
AnotherAPICall = if Record.HasFields(Source, {"url"}) then
Json.Document(Web.Contents(Source[url]))
else
Json.Document(Source[data]),
Result = AnotherAPICall
in
Result,

FinalResultBalanceSheet = GetAPIData(Token, UrlBalanceSheet),
FinalResultProfitAndLoss = GetAPIData(Token, UrlProfitAndLoss),

ProcessedBalanceSheet =
Record.ToTable(FinalResultBalanceSheet),
ProcessedProfitAndLoss =
Record.ToTable(FinalResultProfitAndLoss),
CombinedData =
Table.Combine({ProcessedBalanceSheet, ProcessedProfitAndLoss}),
#"Expanded Value" = Table.ExpandListColumn(CombinedData, "Value"),
#"Expanded Value1" = Table.ExpandRecordColumn(#"Expanded Value", "Value", {"name", "orgName", "type", "reportingCode", "account", "dateMonth", "trackingOption", "code","amount", "year", "month", "headerName"}, {"Value.name", "Value.orgName", "Value.type", "Value.reportingCode", "Value.account", "Value.dateMonth", "Value.trackingOption","Value.code","Value.amount", "Value.year", "Value.month", "Value.headerName"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Value1",{"Value.name"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Name", "Value.year", "Value.month", "Value.orgName", "Value.type", "Value.reportingCode", "Value.account", "Value.dateMonth", "Value.trackingOption", "Value.headerName", "Value.code", "Value.amount"}),
#"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Value.dateMonth", type date}, {"Value.amount", type number}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Value.orgName", "Value.organisation"}})
in
#"Renamed Columns"

This code retrieves new data using your Bearer Token. Be sure to update the Bearer Token placeholder with your actual token copied from Accounting.bi.

  • Save the M Code:

Click "Done" in the Advanced Editor.

  • Set the Bearer Token:

In the Power Query Editor Go to Manage Parameters from Parameters Section.

Click on new parameter and fill in the details to create the new parameter:

  • Parameter Name: Bearer Token
  • Type: Text
  • Suggested Values: Any value
  • Current Value: Paste your Accounting.bi Bearer Token here

Refresh the Data: Now that your Bearer Token is set, the data should automatically update.

Please note:

For a detailed understanding of the sequential procedure for retrieving balance sheet reports and profit and loss account reports from Xero to Accounting BI, you can refer to the following URLs:

Balance Sheet Reports: Link
Profit and Loss Account Reports: Link
For guidance on connecting Xero accounts to Accounting BI, visit this link.

Count on us to assist you every step of the way. Leverage our expertise and guidance today.

learn more

Why is PowerBI important for Accountants?

As an accountant, you may find yourself referring to multiple sources to track the performance of your client’s cash flow, profitability, business trends and even bookkeeping cycle. What if you could access those results and comparisons automatically refreshed, on-demand, from your tablet, smartphone, PC, or even wearable device? Business intelligence and data visualisation systems can provide accountants with – the critical data and organisation summary in a custom format in one (or more) visuals or a dashboard. One of these powerful business intelligence tools is PowerBI.
READ MORE

Power BI: What is DAX?

DAX (Data Analysis Expressions) is a formula expression language developed by Microsoft to interact with data in different analysis services and visualisation tools platforms like Power BI, PowerPivot and SSAS tabular models. DAX can seem familiar to competent excel users as the language uses similar formulas and expressions.
READ MORE

How to connect your Xero account(s) to AccountingBi

This guide will walk you through two methods for connecting your Xero accounting software to AccountingBi
READ MORE

How to call your profit and loss account reports from Xero to AccountingBi

We have outlined a step-by-step process of calling your profit and loss report data from your Xero account to AccountingBi - article and video.
READ MORE

How to call the balance sheet reports from Xero to AccountingBi

We have outlined the steps of calling balance sheet reports from Xero to AccountingBi in a video (watch below) and an article.
READ MORE

How to connect Microsoft excel to Xero through AccountingBi

This video and article provide steps for connecting Xero to PowerBi through AccountingBi. It assumes that you've already pulled the Xero API from accounting BI for your various reports.
READ MORE
1 2 3 5
READ MORE
Accounting.bi - Business Intelligence Software
Copyright © 2023 Accounting.bi is a brand owned and operated by Accounting SQL Limited, incorporated in the United Kingdom. ​
Terms & Conditions - GDPR