LOOKUP VALUE between two dates

Try this JulieP 

I did 2 things. First, I took your table of ranges and converted it to a table that had all dates between the ranges. It looks like this (I used my own data as I wasn't going to key data in from those images)

It originally looked like this:

After the new column, it looks like this:

It used this simple formula in a new column:

{Number.From([Start Date])..Number.From([End Date])}

I then expanded that list of numbers to new rows and changed the type to date.

Then, in the 2nd table, I just merged back to the first table. It looks like this now:

You can see my file here. It is Excel, just open up Power Query. The same logic will work in Power BI. Just set the range table to not load in Power BI, only the final table you need.

You can so an equivalent to a VLOOKUP in Power Query, and Imke might have some more complex code to do it, but you cannot do it simply with a Table.SelectRows and use a filter to < and > as it will be super slow to the point of not completing as the data size grows beyond a few thousand rows. Power Query is horrible at scanning tables. Isn't designed for it.