Forum Discussion
Custom Column, add value from unrelated table based on date range
I'm trying to add a custom column to a table based on a date range in another table. the tables are not related. I can do this in DAX but need it to be in the Query Editor so i can use the updated table in a Merge Query. Here's the data example and result i'm looking to achieve.
Create Label column in table 1
use Date in table 1 and determine into what range (Start and end dates) it falls in table 2
Return label from table 2 to table 1
Table 1
| Date |
| 1/5/2020 |
| 1/18/2020 |
| 2/8/2020 |
Table 2
| Start Date | End Date | Label |
| 1/2/2020 | 1/16/2020 | 2020 01 |
| 1/17/2020 | 1/30/2020 | 2020 02 |
| 1/31/2020 | 2/13/2020 | 2020 03 |
Result
Table 1
| Date | Label |
| 1/5/2020 | 2020 01 |
| 1/18/2020 | 2020 02 |
| 2/5/2020 | 2020 03 |
Use Table.Addcolumn with a custom ColumnGenerator function that uses your logic to return rows from the second table that satisfy your condition, and then pick the required column (Label).
Here is an example for the Table 1 query based on your definition for Table 2:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31TcyMDJQitUBcQwtEDwjfRgnFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]), #"Added Custom" = Table.AddColumn(Source, "Lookup", (k)=> Table.SelectRows(#"Table 2", each (Date.From(k[Date])>= Date.From([Start Date]) and Date.From(k[Date])<= Date.From([End Date]))) ), #"Expanded Lookup" = Table.ExpandTableColumn(#"Added Custom", "Lookup", {"Label"}, {"Label"}) in #"Expanded Lookup"
2 Replies
Use Table.Addcolumn with a custom ColumnGenerator function that uses your logic to return rows from the second table that satisfy your condition, and then pick the required column (Label).
Here is an example for the Table 1 query based on your definition for Table 2:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31TcyMDJQitUBcQwtEDwjfRgnFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t]), #"Added Custom" = Table.AddColumn(Source, "Lookup", (k)=> Table.SelectRows(#"Table 2", each (Date.From(k[Date])>= Date.From([Start Date]) and Date.From(k[Date])<= Date.From([End Date]))) ), #"Expanded Lookup" = Table.ExpandTableColumn(#"Added Custom", "Lookup", {"Label"}, {"Label"}) in #"Expanded Lookup"