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.

[JohnSalt]

Same problem, Same cause, its clearly far from uncommon for people to use Schema as a parameter..why wouldnt it be!

Spent an awful long time trying to trace it, agreed that the query editor gives absoloutly no indication of where the problem lies.

Thanks for the post!

[JohnSalt]

Completely agree with Barry Smart's point that using a parameter that collides with an attribute isnt good practice, as such the change in the April update isnt necessarily wrong. The issue is that the error tracing in the Query editor hasnt been updated to match, same goes for the parameters documentation: Parameters - Power Query | Microsoft Docs

[Jesusdario26]

me he quedado en la version anterior, pues me genero errores en el cargue ya ctualizacion de datos, estas actualizaciones estan trayendo demasiadas incopatibilidades, debi nuemanete hacer el estructurado y modelado, ahora antes de actualizar hago un backup.

[JohnSalt]

@Jesusdario26  - 

Yo también tenía eso, después de una actualización tuve que crear un nuevo modelo y copiar todas mis imágenes del modelo anterior en él.

Actualizar me pone tan nervioso como un gato de cola larga en una habitación llena de mecedoras!

[Anonymous]

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

[JohnSalt]

Presumably 'Schema' is not the only attribute that if used as a parameter will now cause this issue?

I would reitterate the point that its not necessarily a bad thing that since the April 22 update PBI no longer tollerates attributes and parameters carrying the same name. But this change needs to be communicated and the assocaited documentation and error trapping in query editor needs to be updated to reflect this change.

[GSE]

Thanks for posting the solution here Was trying to figure out why my pbix suddenly threw an error. 

I was getting the cyclic reference only on four of my queries but I have other queries without errors. I noticed that only queries where I connect to a source (and that use the parameter Schema in my case) and don't do any other transformations were throwing errors. Other queries where I added even a single transformation step didn't throw an error. In order to test this, I just added a transformation step to on of the four queries that threw an error and voila, the error is gone. Maybe this helps others and/or Microsoft's bug fixers!