Forum Discussion
Merge Tables using foreign key and date range
Not sure why it had to be made so complicated by the other answers.
All I had to do was add a column to Time with the following formula
Table.SelectRows(Assignments, (x) => x[Person] = [Person] and [Date] >= x[StartDate] and (x[EndDate] = null or [Date] <= x[EndDate])){0}?[Role]?All this does is finds related rows in the Assignments table for each row in Time, where the person matches, and the date range is satisfied. We then take the first match (if the data is correct there should always be exactly 1 anyway), and return the Role value from this row. Null is returned on no matches just for safety instead of an error.
This is very closely related to the SQL statement I provided (although I did actually want a left join, not an inner join). There is no need to transform the other tables when handling null values, they can just be handled in the filter.
Hi EoghanSpillane, check this:
Result
let
TblTime = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI00zcw0zcyMDJRitVBCJkjhJzQhGIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, Date = _t]),
ChangedTypeTime = Table.TransformColumnTypes(TblTime,{{"Date", type date}}),
TblAssignments = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1Dcw0zcyMDIBcowNkDhB+TmpuoZKsToIheYwOQWYvBFY3gndILi8sVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Person = _t, StartDate = _t, EndDate = _t, Role = _t]),
ChangedTypeAssignments = Table.TransformColumnTypes(TblAssignments,{{"StartDate", type date}, {"EndDate", type date}}),
ReplacedNull = Table.ReplaceValue(ChangedTypeAssignments, null, Date.From(DateTime.FixedLocalNow()), Replacer.ReplaceValue, {"EndDate"}),
Buffered = Table.Buffer(ReplacedNull),
StepBack = ChangedTypeTime,
Ad_Role = Table.AddColumn(StepBack, "Role", each Table.SelectRows(Buffered, (x)=> x[Person] = [Person] and x[StartDate] <= [Date] and x[EndDate] >= [Date])[Role]{0}?, type text )
in
Ad_RoleI'd be very keen to use this and mark as accepted solution if you can provide an explanation as to what's going on. Not a fan of incorporating a large black box into my report without understanding how it works.