How do I get a value from another table based on a date range

1. 

Load both DateRanges and the second table (with Key and Date) into Power Query.
2. 

Select the Transactions table.
Go to the Add Column tab and select Custom Column.
Write a custom column to check if the date in the Transactions table falls between the CycleStartDate and CycleEndDate in the DateRanges table.

You can write this in a Custom Column:

List.Select(
    DateRanges,
    each [CycleStartDate] <= [Date] and [CycleEndDate] >= [Date]
)

This will return all matching rows from DateRanges. You’ll want to expand that column to include the Cycle value.

After creating the custom column, expand the new column (click on the two arrows next to the column name) and select only the Cycle column from the DateRanges table.
Now your Transactions table will have the Cycle values based on the date range.

3.
Now that your Transactions table has the Cycle, you can merge it with Table1 based on the Key column.

Select Table1 in Power Query.
Click on Home > Merge Queries.
Choose Table1 as the first table and Transactions as the second table.
Select the Key column from both tables to perform the merge.
Use a Left Outer Join so that all rows from Table1 are retained.
After the merge, expand the Cycle column from the Transactions table.

4.

After the merge, Table1 will now contain a Cycle column with the corresponding value from Transactions, based on the Key and Date.
If there’s no matching Key in Transactions, the Cycle will be null.