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 ,
dufoq3 Thanks for your reply!
And EoghanSpillane , inner join will not achieve your desired result because your two tables are in a many-to-many relationship, and Power Query will not be able to match between a single row.
I read dufoq3 's reply and his logic is to use today's date to fill in the missing EndDate and then compare the Date with Start and End. The program is correct.
And I can offer another method.
You can optionally create a custom column in the Time table using the following M Function:
let
_Date = [Date],
_Person = [Person],
MatchRow = Table.SelectRows(Assignments, each [Person] = _Person and [StartDate] <= _Date and [EndDate] >= _Date),
MaxRole=List.Max(Table.SelectRows(Assignments,each [Person]=_Person)[Role]),
Result = try MatchRow{0}[Role] otherwise null
in
if Result<>null then Result else MaxRole
And the final output is as below:
Since there are several rows in your Assignments table that do not have an EndDate, I'm guessing that for each different Person, the row that does not have an EndDate is the last row for their Person.
So my logic is: compare the Date column in the Time table with the StartDate and EndDate in the Assignments table respectively, and if the Date falls within that date range interval and the Person is the same, return the Role for the corresponding row in the Assignments table. If the Date does not exist in any of the Start-End intervals, the Assignment has not yet ended, i.e., there is no EndDate, then return the largest Role corresponding to the Person in the Assignments table.
Here is the whole M function in the Advanced Editor:
let
Source = 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]),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-GB"),
#"Added Custom" = Table.AddColumn(#"Changed Type with Locale", "Custom", each let
_Date = [Date],
_Person = [Person],
MatchRow = Table.SelectRows(Assignments, each [Person] = _Person and [StartDate] <= _Date and [EndDate] >= _Date),
MaxRole=List.Max(Table.SelectRows(Assignments,each [Person]=_Person)[Role]),
Result = try MatchRow{0}[Role] otherwise null
in
if Result<>null then Result else MaxRole)
in
#"Added Custom"
Best Regards,
Dino Tao
If this post helps, then please consider Accept both of the answers as the solution to help the other members find it more quickly.