Forum Discussion
date period
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.
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
6 Replies
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 selectThe 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:
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
In power query.
