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

[Barry_Smart]

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:

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.

[jbaconpbi]

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

[Data-Rainer]

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

[Anonymous]

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]

Thanks for your response @Anonymous.  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]

Dear @Anonymous 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]

Dear @Anonymous, 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.

[Anonymous]

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]

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]

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

[DavidMcCausland]

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.