cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
numersoz
Helper III
Helper III

Lookup From A Table If Value Is Between Two Other Columns

Hi,

I have these two tables. For the table with Timestamp, I need to fill the ID column from the other table, if the timestamp value is between the values of columns Start Date and End Date.

Any suggestions on how this can be done?

 

TIMESTAMPCOL 1COL 2ID
1/1/2019 12:00:00 AM +00:005.990?
1/1/2019 1:00:00 AM +00:009.460?
1/1/2019 2:00:00 AM +00:009.80?
1/1/2019 3:00:00 AM +00:009.80?
1/1/2019 4:00:00 AM +00:009.820?
1/1/2019 5:00:00 AM +00:0010.051.35?
1/1/2019 6:00:00 AM +00:0010.119.87?
1/1/2019 7:00:00 AM +00:009.989.89?
1/1/2019 8:00:00 AM +00:0010.039.81?
1/1/2019 9:00:00 AM +00:0010.019.92?
1/1/2019 10:00:00 AM +00:009.979.93?

 

IDStart DateEnd Date
11/1/20201/2/2020
21/2/20201/3/2020
31/3/20201/4/2020
41/4/20201/5/2020
51/5/20201/6/2020
61/6/20201/7/2020
71/7/20201/8/2020
81/8/20201/9/2020
91/9/20201/10/2020
101/10/20201/11/2020
111/11/20201/12/2020
1 ACCEPTED SOLUTION

Hi @numersoz ,

The Custom column represents any date between Startdate and EndDate for each ID.

For example, from January 1st to February 1st, you would have a line per day where the ID = 1 that would allow you to merge both tables like this:

Payeras_BI_0-1614264879106.png

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

View solution in original post

6 REPLIES 6
Payeras_BI
Super User I
Super User I

Hi @numersoz ,

In PQ you could add a Custom column to your Start/End date table with the code provided below:

List.Dates([Start Date],(Number.From([End Date])-Number.From([Start Date])+1),#duration(1,0,0,0))

Payeras_BI_1-1614245201195.pngPayeras_BI_2-1614245244595.png

And then merge both tables to get the ID using Timestamp and Custom column (now at the same granularity).

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

@Payeras_BI Thank you for your reply. I'm trying to understand the logic here, did you assume that the start date and end date would be only 1 day apart from each other? It could also be multiple days apart.

 

Also, you are only making it match the start datetime. But the timestamp has to be any value between the start date and end date.

Hi @numersoz ,

The Custom column represents any date between Startdate and EndDate for each ID.

For example, from January 1st to February 1st, you would have a line per day where the ID = 1 that would allow you to merge both tables like this:

Payeras_BI_0-1614264879106.png

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

View solution in original post

AlexisOlson
Community Champion
Community Champion

With your particular example, there are no matches (2019 vs 2020+) but ignoring that, you should be able to turn the timestamp into a date and lookup the [ID] where that date matches [Start Date] in the Other table.

ID = LOOKUPVALUE ( Other[ID], Other[Start Date], INT ( Table1[TIMESTAMP] ) )

Hi @AlexisOlson , thank you for the reply but there is a bit more to it compared to a basic lookup.

I need to get the ID number only if the timestamp value is between the start and end dates.

More generally, you could write it like this:

ID =
MAXX (
    FILTER (
        Other,
        Table1[TIMESTAMP] >= Other[Start Date]
            && Table1[TIMESTAMP] < Other[End Date]
    ),
    Other[ID]
)

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors