Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
saffronbows
Frequent Visitor

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:

YRTRBEGIN_DATELead.BEGIN_DATE
202251/9/20235/30/2023
202338/22/20221/9/2023

 

Thank you

3 REPLIES 3
v-cgao-msft
Community Support
Community Support

Hi @saffronbows ,

Please try like:

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

vcgaomsft_0-1718008695138.png

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

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]))"

lbendlin
Super User
Super User

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

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/1447...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors