date period

[chuyiyi]

Dear all, good day 

Two tables:

Table 1 has these fields , Start Date 、End Date 、Location.

Table 2 has Date Filed , how to estimate the date in which period and get the Location Value.

Please see screenshot below.

[AlienSx · Accepted solution]

Hello, @chuyiyi 

let
    Table1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Table2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    combo = Table.RenameColumns(Table1, {"Start Date", "Order Date"}) & Table.AddColumn(Table2, "mark", each 1),
    sort = Table.Sort(combo,{{"Order Date", Order.Ascending}, {"mark", Order.Descending}}),
    fdown = Table.FillDown(sort,{"Location"}),
    filter = Table.SelectRows(fdown, each ([mark] = 1)),
    select = Table.SelectColumns(filter,{"Location", "Order Date"})
in
    select

[foodd]

The question and visually efficient pattern caught my attention. 

While adding this pattern to a project I had open, I noticed

that if you add new values to Table 2 that are not in a range of

Table 1, there is no logic to handle this condition and say

return a blank or null.   Would you have a moment to add 

handling for an out-of-range condition?

Table 1 lookup ranges include:

Update Table 2 entries to include 2018-12-31 and 2023-01-01 

that are not included in the Table 1 ranges.

I would have expected 2018-12-31 to return blank, however

would not have expected 2023-01-01 to return a match of 

Location C.

Query Results:

[AlienSx]

Hi, @foodd how about this? 

let
    Table1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Table2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    combo = Table.AddColumn(Table1, "Order Date", each [Start Date]) & Table.AddColumn(Table2, "mark", each 1),
    sort = Table.Sort(combo,{{"Order Date", Order.Ascending}, {"mark", Order.Descending}}),
    fdown = Table.FillDown(sort,{"Location", "End Date"}),
    filter = Table.SelectRows(fdown, each ([mark] = 1)),
    later = Table.ReplaceValue(filter, each [Order Date] > [End Date], null, (v, o, n) => if o then n else v, {"Location"}),
    select = Table.SelectColumns(later,{"Location", "Order Date"})
in
    select

[foodd]

@AlienSx, thank you.   This works well. 

[chuyiyi]

@AlienSx  

It's such an ingenious code.

Thank you very much.

[chuyiyi]

In power query.