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

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.