Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Barry_Smart

Exception using Parameter in Power Query: A cyclic reference was encountered during evaluation.

Submitted by on ‎04-20-2022 07:47 AM

Hello,

 

Having downloaded the latest version of Power BI desktop (Version: 2.104.702.0 64-bit (April 2022)), we are now encountering an "A cyclic reference was encountered during evaluation" exception when refreshing the data from a MySQL database:

 

Barry_Smart_1-1650464604676.png

 

We've traced the issue to the use of a Parameter named `Schema` in the Power Query as follows:

let
    Source = MySQL.Database(Server, Database, [ReturnSingleDatabase=true]),
    <query name> = Source{[Schema=Schema,Item="<table name>"]}[Data]
in
    <query name>

 

A refresh of the data preview in the Power Query Editor works without any issues, it is only when we attempt to reload the data in the Power BI report that the exception is generated.

 

The issue is with the following specific snippet in the query:

 

Schema=Schema

 

 

The solution / workaround is to rename the Parameter to something other than "Schema".  For example, the reload works as it did before if we change the query to use a new Parameter called "SourceSchema":

let
    Source = MySQL.Database(Server, Database, [ReturnSingleDatabase=true]),
    <query name> = Source{[Schema=SourceSchema,Item="<table name>"]}[Data]
in
    <query name>

 

Whilst the use of a Parameter name which collides with a Power Query attribute name isn't a good principal to adopt, the concern is that this code has been working for months and is only generating an exception with the new version of Power BI Desktop.

 

The concern is that other users may have adopted this convention in their own Power BI reports and will therefore encoutner the same issue where:

  • The source of the problem is not straightforward to isolate;
  • Issues with the use of a Parameter called "Schema" in this context is not immediately apparent in the the Power Query Editor where data preview refresh works without any issues.  It only surfaces when you reload / refresh the data in the Power BI report itself;
  • There is a risk that this behaviour could propagate into the Power BI Service where report refreshes could being to fail.  This would create a significant operaitonal issue where Power BI reports are being used in production.
Status: Accepted

Hi @Barry_Smart ,

 

We have submitted to internal ICM 304873937 , the status of this thread will be changed to Accepted to prove that this is a bug confirmed by Microsoft.

 

We have gotten some feedback from the PG team：

 

“The cyclic reference is because the record field name is Schema, and the customer is trying to assign the value of Schema to be Schema.  This means that in order to evaluate the "Schema" field, the engine first has to evaluate the "schema" field, producing infinite recursion.  This is not a problem with the connector, but with the customer's mashup query.  Query editor uses different queries to produce (and refresh) the preview data, so the cyclic reference does not become immediately problematic until refresh when the customer's query is ran.

 

There is a simple mitigation for this: clients may avoid using schemas that have a name called schema, or referencing them from a different schema name.”

 

The bug has been submitted but there is no clear time frame for fixing it yet. Engineers are trying their best to solve this issue, please be patient and I will give you feedback here once there is any progress.

 

Best Regards,
Community Support Team _ Caitlyn

See more ideas labeled with:
17 Comments (17 New)
Comments
jbaconpbi
Regular Visitor
‎04-20-2022 12:18 PM

Thanks for posting! I am experiencing a very similar issue and it's taken all day to try and diagnose. 

Data-Rainer
Advocate IV
Advocate IV
‎04-20-2022 06:27 PM

Thanks a lot for posting the fix from us too. 👍 We had the very same issue and also spent hours trying to diagnose.

v-xiaoyan-msft
Community Support
Community Support
‎04-20-2022 06:51 PM
Status changed to: Delivered

Hi @Barry_Smart ,

 

Thank you for sharing and feedback, other users who encounter similar problems also could check the steps in the 'Applied Steps' area in Query editor to find which step is the problem. You may click from the upper step to the lower step in turn, and see which step appears the corresponding error message.

 

As for the suggestion you made. You may vote the idea here  and comment there to improve this feature. It is a place for customers provide feedback about Microsoft Office products . What’s more, if a feedback is high voted there by other customers, it will be promising that Microsoft Product Team will take it into consideration when designing the next version in the future.

 

Best Regards,
Community Support Team _ Caitlyn

Barry_Smart
Regular Visitor
‎04-21-2022 12:26 AM

Thanks for your response @v-xiaoyan-msft.  I tried your suggestion of stepping through the Applied Steps in the Query Editor, and all looks fine.  It is not until I "Close and Apply" or choose to Refresh the main Power BI report that the exception occurs.

Data-Rainer
Advocate IV
Advocate IV
‎04-21-2022 12:34 AM

Dear @v-xiaoyan-msft I think you are missing the point / issue here.
I agree with @Barry_Smart. We also looked everywhere for clues for the error and could not find them anywhere, also not in any of the steps in Power Query.

This error is solely due to the April 2022 Update of Power BI Desktop 
However the newly enforced logic of the update does not show the cause of the error anywhere, hence @Barry_Smart 's solution is gold and needs to be promoted to an urgent update to all Power BI Desktop users.

Barry_Smart
Regular Visitor
‎04-21-2022 02:17 AM

Dear @v-xiaoyan-msft, please can we challenge the status that has been applied to the issue.

 

The status has been set straight to "Delivered" with no actual mitigation / acceptance of the issue.

 

The suggestions you have made are indicative of not fully understanding the nature of the issue.  Please let me know if you need further clarification, happy to provide that.

 

We need to re-open the issue so that it can be investigated properly.

v-xiaoyan-msft
Community Support
Community Support
‎04-21-2022 02:47 AM
Status changed to: Investigating

Hi @Barry_Smart ,

 

Sorry for my lose.

Based on the above information, if you are a Power BI Pro licensee, you can create a support ticket for free and a dedicated Microsoft engineer will come to solve the problem for you.
It would be great if you continue to share in this issue to help others with similar problems after you know the root cause or solution.

 

The link of Power BI Support: Support | Microsoft Power BI

For how to create a support ticket, please refer to How to create a support ticket in Power BI - Microsoft Power BI Community

 

Best Regards,
Community Support Team _ Caitlyn

mh-gd
Helper I
Helper I
‎04-22-2022 07:32 AM

Thanks as well for your post, this has now solved our issue as well. Please keep us informed if there are new information on the "why".

GenieG
Regular Visitor
‎04-27-2022 04:56 PM

Thank you for sharing! This just saved us a lot of time. 

DavidMcCausland
New Member
‎05-05-2022 02:04 AM

Hi,

 

We are experiencing a very similar issue using parameters when connecting to spark tables in Azure Databricks, again starting with the April 2022 version of PowerBI desktop. For now we have downgraded to an earlier version of PowerBI Desktop.

 

This occurs when bringing in a single table to an empty report using the following code:

 

let
    Source = Databricks.Contents(HostName, HTTPPath, null),
    SPARK_Database = Source{[Name="SPARK", Kind="Database"]}[Data],
    Schema = SPARK_Database{[Name=DatabaseName, Kind="Schema"]}[Data],
    Table_Name = Schema{[Name="Table_Name", Kind="Table"]}[Data]
in
    Table_Name

 

The data preview in Power Query Editor loads without issue. When "Close & Apply" is clicked, the import fails with the error "A cyclic reference was encountered during evaluation".

 

The issue dissapears when anything is done to the table, e.g. renaming a column, casting a column, changing a locale of a date etc.

Idea Statuses