Forum Discussion
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 range of dates in my Lookup table. I want the column to be the related Lookup.[YRTR] value where [Calendar.Date] >= [Lookup.BEGIN_DATE] and [Calendar.Date] < [Lookup.Lead.BEGIN_DATE].
My lookup table has a couple hundred ordered values, each with a unique BEGIN_DATE. To this lookup table I have added Lead.BEGIN_DATE to be the BEGIN_DATE of the subsequent record. Here is a snippet:
| YRTR | BEGIN_DATE | Lead.BEGIN_DATE |
| 20225 | 1/9/2023 | 5/30/2023 |
| 20233 | 8/22/2022 | 1/9/2023 |
Thank you
3 Replies
Note that a "calculated column" is not something you do in Power Query, it is a DAX thing.
In Power Query you would use List.Dates(), and in DAX you would use CALENDAR().
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hi 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 TeamIf 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
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]))"
