"Expression.Error: A cyclic reference was encountered during evaluation." After Remove Row Datasourc

[Anonymous]

Hi,

I have a report, which is based on an excelsheet.

Yesterday, without thinking I deleted some columns, which I know are not used in the Power BI reporting or anywhere else, and then the problem started occuring.

Due to the fact that I uploaded the excelsheet to Power BI Desktop before removing these columns and then deleted them inside our desktop program, it tells me it does not "recognise" the Columns by the Name "XYZ,"YZX", etc.

I then removed the "Removed Rows" etc. from the M-Query, but now I am encountering a problem I cannot seem to fix:

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

It should be mentioned I have 3 tables in the Desktop version, based on the same table from Excel, is this the problem and how do I fix it? I am quite desperate as I put in a lot of work and now the report does not refresh anymore...

[beckemeyer]

I would suggest that many of these issues are coming from a really extensive query that appears to have many identical steps. Combining those steps and then looking at the advanced editor for references to the columns that you deleted should make a huge difference, both in query performance and in diagnosing issues. For example, I'm seeing a ton of joins followed by removing columns and then renaming them. Without importing the prefixes, could you correct the columns to what you need in the source table and then when you expand, only bring over the columns needed? I would also suggest looking at solving some of those joins through schema to save on query time.

Start by streamlining the query and expanding the schema, and then check for column references inside of the advanced editor.

[kky1]

I received this error alongside an ODBC error S1000. Does anyone have suggestions on what may have caused this issue? I believe it was likely recent changes to the ODBC data source but am trying to troubleshoot what sort of change may have caused it. Thank you.

[v-alq-msft]

Hi, @Anonymous 

Please check the steps in the 'Applied Steps' area in Query editor to find which step is the problem.

Best Regards

Allan

[Anonymous]

@v-alq-msft 

Thank you for your reply.

Unfortunately I do not know how to, as it does not give an error in the M-Query:

*Removed*

When I press the different step in the "APPLIED STEPS" it gives me the following error when I press on the first step:

Expression.Error: We cannot convert the value 0 to type Text.
Details:
Value=0
Type=[Type]

[v-alq-msft]

Hi, @Anonymous 

The screenshot displays your private sharepoint url information. Please mask sensitive data before uploading for safety. Thanks.

About the issue, It is not to say that there is something wrong with the symbol. Instead, you may click from the upper step to the lower step in turn, and see which step appears the corresponding error message.

Best Regards

Allan

[Anonymous]

@v-alq-msft 

Hi Allan,

I have deleted the photo, thanks for giving me the notice.

I have gone through all steps and it seems these steps give the error:

Whereas the rest give this:

[ProgramSam]

The issue here was that under "Applied Steps", the step before "Changed Type1" was referencing "Removed Columns3" when it should have been referencing "Removed Columns2".  Had you simply changed the number "3" to a "2" at the end of the name, the query would have worked. 🙂

[v-alq-msft]

Hi, @Anonymous 

I wonder if 'Removed Columns2' step can get the result normally. If not, please find out the last step which can get the result normally.

Best Regards

Allan

[Anonymous]

@v-alq-msft,

I ended up redoing the report, thank you anyways for your comment.

[Anonymous]

Bump, does anyone know how to solve this problem?

[Anonymous]

Very desperate, does anyone know how this can be fixed?

[ThePiecesFit]

While this might not help your particular situation, what solved it for me just now is refreshing everything again.