How to solve the Power Query "Privacy Firewall Error"
August 28, 2024
Tired of the Power Query "Privacy Firewall Error" : Formula.Firewall: Query 'Query1' (step 'FinalResult') is accessing data sources that have privacy levels which cannot be used together. Please rebuild this data combination.''? blocking your Xero connection in Acc.Bi?
Watch this video and conquer this error in minutes!
Understanding the Error
This error typically occurs when connecting to a web API using the http:header parameter with a bearer token, when retrieving data from Xero into Microsoft Excel or Power BI through AccountingBi through the method of leveraging the Power Query M code. The problem arises because Power Query's privacy settings prevent the combination of data from sources with differing privacy levels, causing the "Formula.Firewall" error.
Step-by-Step Solution
To resolve this issue, you'll need to adjust the privacy settings of your data sources within Power Query. Here’s how to do it:
Access Data Source Settings: Go to the 'Data Source Settings' within Power Query. This error indicates that some data sources in your M code have different privacy settings. In this scenario, the privacy setting for Acc.Bi and the URL you’re attempting to connect to (such as the Profit and Loss report URL) need to be compatible.
Edit Permissions: Locate the relevant data sources and click on "Edit Permissions". Change the privacy setting from "Private" to "Public", as required by the API. This adjustment allows the data sources to be combined correctly.
Confirm Changes: Click 'OK' to save the changes, then close the settings window.
Refresh the Query: Once the permissions are updated, refresh the query. Click on 'Refresh Preview' to ensure the changes are applied correctly.
Convert Data into Table: Next, click on 'Into Table'. Under the 'Convert' section, select 'Insert'.
Expand to New Rows: Click on the double arrow icon and select 'Expand to New Rows'. Repeat this step to fully expand the data as needed.
Finalise the Table: Once you’ve expanded the data, click on the double arrow again and click 'OK'. Your data should now be displayed correctly in table format.
By following these steps, you should be able to resolve the "Privacy Firewall Error" and ensure that your data sources in Power Query are compatible, allowing you to successfully connect and work with your Xero data in Excel or Power BI.
"Don't hesitate to contact us for expert guidance and solutions."
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.
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.
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.
