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