How to Connect to Xero from Power Query

Connecting Xero to Power Query can significantly enhance your data analysis capabilities. By following these simple steps, you'll be able to import and manipulate your Xero data directly within Excel, making your financial reporting more efficient and insightful.

Access Power Query Editor

Open Excel and go to the "Data" tab.
Click on "Get Data", choose "From Other Sources", and then select "Blank Query".

Open Advanced Editor

In the Query section, click on "Advanced Editor".

Paste M Code

Replace the placeholders with your Bearer Token and API URL in the provided M code.
Paste the modified M code into the Advanced Editor.

M Code:

let
Token = "Bearer (Paste the Bearer Token)",
Url = "(Paste the Report API URL Here)",

GetAPIData = (token 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,
FinalResult = GetAPIData(Token)
in
FinalResult

Note: For detailed steps on retrieving balance sheet and profit and loss account reports from Xero to Accounting BI, check out these links: Balance Sheet Reports and Profit and Loss Reports. For guidance on connecting Xero accounts to Accounting BI, visit Connecting Xero Accounts.

Confirm and Exit

Review the code and click "Done" to exit.

Edit Credentials

Click "Edit Credentials", then "Connect". Set the Privacy level to "Public" and save.

Convert to Table

In the query editor, click "Into Table".

Expand Rows

Click the double-sided arrow icon, select "Expand to new Rows", and click "OK".

View Data

Your API data is now displayed in the query editor.

This method uses Power Query's web import capabilities and requires a subscription for pre-built templates (optional).

Need help connecting Xero accounts to Accounting BI? Find all the details here.

Now you’re all set to bring your Xero data into Power Query like a pro. 🎉

Ready to take your financial management to the next level with AccountingBI? Join the growing number of businesses that trust our expert tax advisors and accountants for seamless integration with Xero. Sign up today and let our team of professionals help you streamline your accounting processes, gain deeper insights, and achieve your financial goals with ease. Don't wait—experience the difference now!

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.

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.