Forum Discussion
Expression.Error: A cyclic reference was encountered during evaluation.
Hi all.. I am wondering if any of you amazing POWER BI Power Query Users can help me understand better the Expression.Error: A cyclic reference was encountered during evaluation.
I am trying to add a Custom Column during the transform process, immediately after establishing my source from a SQL query.
The column I am trying to add is to evaluate Table 1 [Date Column] between Table 2 [Start Date] and Table 2 [End Date].
I am using the following code:
Table.AddColumn(
Table1,
"Contract Year",
each if [posting_date] >= Table2[Start Date] and [posting_date] <= Table2[End Date]
then Table2[Contract Year]
else null
)
I cannot determine:
1. Is the "cyclic" in reference to the code?
Things to know:
There are no relationships established between my main table to any other table.
There are no additional calculated, or other, columns that have been added at this point.
In my mind, this table is importing from a database clean, fresh, untouched data, so there should be no cyclic reference to anything else. I also realize that I may have a complete lack of understanding of this error. PLEASE HELP!! (Still learning... )
FYI - I have attempted to create this column using "Merge Queries", but do not see the option to change the Join Kind to a Custom Join that will allow the BETWEEN dates functionality that I need.
The error could be thrown if Table2 has Table1 as its source (or is referenced).
EG.
Table1
Table2
Is created by referencing selecting rows from Table1
= Table.SelectRows(Table1, each ([Contract] = "ABC"))Trying to add a column to Table1 with Table2 as criteria throws the cyclic reference error.
If I had a third table that was not based on Table1
The cyclic error would not appear if Table3 is used as criteria. In this case I do get a type comparison error (which you will likely get as it appears you are comparing a date in Table1 to the list of dates from Table2)
To clear the type comparison error you would need to select a single value from your Table2 as opposed to the list.
To be clear, fixing the list issue will not get rid of the cyclic problem. The cyclic problem can only be fixed by correcting the table reference.
But as far as list values go...
Consider Table1->and Table2 ->
If we want to get a single date value from Table2 into Table1 where the Categories match it could look like this...
In Table1 add a custom column...This returns the list of Dates from Table2 where the Category matches.
You need to know which value from the list you need. Do you need the max date?
Do you need the first date? (Using List.First)
OR (Using the 0th index value of the list)...
Understanding and using lists opens up a whole new level of options in your M code.
Here is the MS Learn document for List Functions.https://learn.microsoft.com/en-us/powerquery-m/list-functions
Hope this helps.
Why the cyclic reference error happens:
In Power Query, Table2[Start Date] does not mean “for each row of Table2, grab Start Date”.
Instead, it returns the entire column as a list.
When you write it this way inside Table.AddColumn, PQ doesn’t know which row of Table2 you mean → it ends up trying to self-reference while evaluating, which triggers the cyclic reference error.
Since what you want is a between join (posting_date falls between [Start Date] and [End Date] in Table2), you can’t do it directly in Table.AddColumn. Instead you need to use a cross join + filter
Add a dummy column to both tables (e.g. column with value = 1).
Merge queries on that dummy column → you’ll get all combinations of Table1 × Table2.
Expand Table2.
Add a custom column with your condition:
if [posting_date] >= [Start Date] and [posting_date] <= [End Date]
then [Contract Year] else nullFilter out nulls if you want only matching rows.
This works well if Table2 is small (e.g. contract periods table with dozens/hundreds of rows, not millions).
7 Replies
The error could be thrown if Table2 has Table1 as its source (or is referenced).
EG.
Table1
Table2
Is created by referencing selecting rows from Table1
= Table.SelectRows(Table1, each ([Contract] = "ABC"))Trying to add a column to Table1 with Table2 as criteria throws the cyclic reference error.
If I had a third table that was not based on Table1
The cyclic error would not appear if Table3 is used as criteria. In this case I do get a type comparison error (which you will likely get as it appears you are comparing a date in Table1 to the list of dates from Table2)
To clear the type comparison error you would need to select a single value from your Table2 as opposed to the list.
Thank you for this explanation. I agree that it is more likely the fact that I am comparing a date in Table1 to the list of dates from Table2. Would it be possible to use the List.PositionOf in this situation (evaluating between 2 dates)? I did another Custom Column on a different table that evaluated only 1 criteria using this but am not sure how to translate it to the 2 dates evaluation. My other column was coded like this:
= Table2[Result]{List.PostitionOf(Table2[Category],[Category])}I understand that with the above code I am looking at only 1 search criteria and it is a 1 for 1. I am struggling to get to where I need in order to transform another column where I bring the category over based evaluating the dates.
To be clear, fixing the list issue will not get rid of the cyclic problem. The cyclic problem can only be fixed by correcting the table reference.
But as far as list values go...
Consider Table1->and Table2 ->
If we want to get a single date value from Table2 into Table1 where the Categories match it could look like this...
In Table1 add a custom column...This returns the list of Dates from Table2 where the Category matches.
You need to know which value from the list you need. Do you need the max date?
Do you need the first date? (Using List.First)
OR (Using the 0th index value of the list)...
Understanding and using lists opens up a whole new level of options in your M code.
Here is the MS Learn document for List Functions.https://learn.microsoft.com/en-us/powerquery-m/list-functions
Hope this helps.
Why the cyclic reference error happens:
In Power Query, Table2[Start Date] does not mean “for each row of Table2, grab Start Date”.
Instead, it returns the entire column as a list.
When you write it this way inside Table.AddColumn, PQ doesn’t know which row of Table2 you mean → it ends up trying to self-reference while evaluating, which triggers the cyclic reference error.
Since what you want is a between join (posting_date falls between [Start Date] and [End Date] in Table2), you can’t do it directly in Table.AddColumn. Instead you need to use a cross join + filter
Add a dummy column to both tables (e.g. column with value = 1).
Merge queries on that dummy column → you’ll get all combinations of Table1 × Table2.
Expand Table2.
Add a custom column with your condition:
if [posting_date] >= [Start Date] and [posting_date] <= [End Date]
then [Contract Year] else nullFilter out nulls if you want only matching rows.
This works well if Table2 is small (e.g. contract periods table with dozens/hundreds of rows, not millions).
Hi adentler ,
Thank you for engaging with the Microsoft Fabric Community.The jgeddes explanation about the cyclic reference issue is accurate. You may also find these related discussions useful, as they cover similar cases and offer possible solutions.
Solved: Expression.Error: A cyclic reference was encounter... - Microsoft Fabric Community
Solved: Suddenly "Expression.Error: A cyclic reference was... - Microsoft Fabric Community
These threads include more examples and methods that could help you understand and address the issue.
Thanks for your response jgeddes Omid_MotamediseRegards,
Yugandhar.jgeddes Omid_Motamedise Thank you both for your guidance and assistance on this item. I have a better understanding of what is going on and am still working through this issue. I am going to read through the links provided and re-read (probably several times!) your comments. For now, I am going to close this item as resolved in hopes that others will also benefit from the information you both have provided.
