Well Known Power Query Errors and How to Handle Them

Power Query is a powerful tool used in Power BI, Excel, and other Microsoft products to clean, transform, and load data. While it simplifies data preparation, users often encounter various errors that can disrupt workflows and hinder progress. Understanding these errors and knowing how to resolve them is crucial for any data professional. In this blog post, we will explore some common Power Query errors, their causes, and practical solutions to fix them.

1. Formula.Firewall Error

What It Is:

The “Formula.Firewall” error typically occurs when Power Query combines data from different sources with different privacy levels (such as combining data from a public web page with a private internal database). This error is a part of Power Query’s privacy protection mechanism.

How to Fix It:

  • Adjust Privacy Levels: Go to File > Options and settings > Options > Privacy. You can change the privacy level for each data source to either Public, Organizational, or Private. Setting both sources to the same level (e.g., Public or Organizational) can resolve the issue.
  • Ignore Privacy Levels: If privacy isn’t a concern in your scenario, you can disable the privacy level checks altogether by selecting “Ignore the Privacy Levels.” However, this is not recommended for sensitive data.

2. Expression.Error: A cyclic reference was encountered during evaluation

What It Is:

This error occurs when there is a circular dependency in your query steps. For example, if Query A depends on Query B, and Query B, in turn, depends on Query A, Power Query cannot determine the sequence of operations, resulting in a cyclic reference error.

How to Fix It:

  • Check Dependencies: Review your queries to identify the circular reference. Open the “Query Dependencies” view in Power Query Editor (View > Query Dependencies) to visualize the relationships between your queries.
  • Break the Loop: Adjust the queries to eliminate the circular dependency. This might involve restructuring your queries or using intermediate steps to break the cycle.

3. DataFormat.Error: Invalid cell value ‘#N/A’ found

What It Is:

This error occurs when Power Query encounters unexpected values (like #N/A, NULL, or special characters) that do not conform to the expected data type of a column. For example, attempting to convert a text column containing #N/A to a number will result in a DataFormat.Error.

How to Fix It:

  • Replace Errors: Use the Replace Errors feature in Power Query (Home > Replace Errors) to replace problematic values with a default value like 0 or “Unknown.”
  • Convert Data Types Carefully: Before converting data types, check for inconsistent values by sorting or filtering the column. Use Remove Errors to eliminate rows with such issues or Replace Values to handle specific cases.

4. Formula.Error: There was an error in the query

What It Is:

This is a generic error message that can occur for various reasons, such as syntax errors, invalid function names, or incorrect parameters within a formula.

How to Fix It:

  • Check Syntax: Double-check the syntax of your query. Look for missing commas, brackets, or other punctuation marks.
  • Use Intellisense: Power Query’s formula bar provides Intellisense suggestions that can help you identify and correct syntax errors.
  • Review Applied Steps: Go through each step in the Applied Steps pane to identify where the error first occurs. Fix the error in the corresponding step.

5. “DataSource.Error: Web.Contents failed to get contents from…”

What It Is:

This error occurs when Power Query fails to access data from a web source. This could be due to several reasons like an invalid URL, server downtime, or internet connectivity issues.

How to Fix It:

  • Check the URL: Ensure the URL is correctly entered and accessible in your browser.
  • Adjust Connection Settings: Go to Data source settings and ensure that the correct authentication and privacy settings are applied for the web source.
  • Retry Later: Sometimes, the issue is temporary, such as server downtime. Wait a few minutes and try refreshing the query again.

6. OLE DB or ODBC Error: [DataSource.Error]…

What It Is:

This error often appears when connecting to external databases using OLE DB or ODBC drivers. It can be caused by driver issues, incorrect connection strings, or database permissions.

How to Fix It:

  • Check Connection Details: Verify that the server name, database name, and credentials are correct in the connection string.
  • Install or Update Drivers: Make sure that the appropriate OLE DB or ODBC drivers are installed and up-to-date.
  • Database Permissions: Ensure that the user account has the necessary permissions to access the database. Contact your database administrator if needed.

7. Access to the resource is forbidden

What It Is:

This error occurs when Power Query is denied access to a data source due to insufficient permissions. This is common when dealing with APIs, web sources, or restricted network resources.

How to Fix It:

  • Authentication Settings: Go to Data source settings and reconfigure the authentication type (Anonymous, Basic, Windows, OAuth, etc.) to match the requirements of the source.
  • API Keys or Tokens: If you are using an API, ensure that the correct API key or token is being used and has not expired.
  • Network Access: Check if any firewall or network settings are blocking access to the data source.

Conclusion

Errors in Power Query can be frustrating, but understanding the root cause and knowing the appropriate fixes can save you a lot of time and effort. By familiarizing yourself with these common errors and their solutions, you can enhance your data transformation skills and create more robust and error-free data models in Power BI.