Forum Discussion

saffronbows's avatar
saffronbows
Frequent Visitor
2 years ago

Create calculated column in Calendar table from range of dates in lookup table.

Hello, first post here. Please let me know how I might make my problem easy to reproduce. I am working in Power Query.   I would like to create a calculated column in a basic Calendar table from ra...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi saffronbows ,

Please try like:

(x) => Table.SelectRows(Lookup,each x[Date] >= [BEGIN_DATE] and x[Date] < [Lead.BEGIN_DATE])[YRTR]

Best Regards,
Gao
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

saffronbows's avatar
saffronbows
Frequent Visitor
2 years ago

Thank you, Gao. Only a single value should be returned from the lookup table. When I try to expand the list I get "Expression.Error: A cyclic reference was encountered during evaluation." Am I interpreting your syntax correctly? From the query editor, my step looks like "= Table.AddColumn(#"Previous Step", "YRTR", each (Table.SelectRows('Lookup', each 'Calendar'[Date] >= [BEGIN_DATE] and 'Calendar'[Date] < [Lead.BEGIN_DATE])[YRTR]))"