Forum Discussion
Expression.Error: A cyclic reference was encountered during evaluation.
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).
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.
Your explanation and time provided are very much appreciated. I will review all that you provided and also the link about list functions. I think both will help me gain understanding.
I am actually not trying to pull the first date or the max date.. instead, I am trying to get the Contract Year. My reference table Table 2 is structured as follows:
Acct Code Contract Year Start Date End Date Cost Center 00123AA Year 1 07/01/2024 06/30/2025 1111 10123AA Year 2 07/01/2025 06/30/2026 1111 00223AB Year 1 07/01/2024 06/30/2025 2222 10223AB Year 2 07/01/2025 06/30/2026 2223 As you can see, Cost Center and Acct Code have a many to many correlation but are distinguished by the Contract Year.
Table 1 is structured as follows:
Cost Center Doc No posting_date Amount 1111 ABC-123 07/5/2025 1000.00 1111 ABC-005 03/15/2024 2000.00 2222 ABB-105 05/02/2024 3000.00 2222 ABC-200 09/05/2025 1500.00 Using DAX, I was able to add a calculated column after import. However, what I have been racking my brain is how to do it during the transform procress (and avoid the cyclic reference, hence my original post you were kind enough to respond to).
I only am adding my 2 source tables as reference in hopes I make it clearer (more for my purposes I think).
I was hoping to end up with a Table 1 that looks like this:
Cost Center Doc No posting_date Amount Contract Year 1111 ABC-123 07/5/2025 1000.00 Year 2 1111 ABC-005 03/15/2024 2000.00 Year 1 2222 ABB-105 05/02/2024 3000.00 Year 1 2222 ABC-200 09/05/2025 1500.00 Year 2 I can see by your explanation that the List of Start Date/ End Date may be the issue and I need to start there.