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

[adentler]

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.

[jgeddes · Accepted solution]

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.

[jgeddes · Accepted solution]

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.

[adentler]

@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.

[V-yubandi-msft]

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_Motamedise 

Regards,
Yugandhar.

[Omid_Motamedise · Accepted solution]

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 null

Filter 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).

[jgeddes · Accepted solution]

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.

[adentler]

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.

[jgeddes · Accepted solution]

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.

[adentler]

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.