Merge tables where Date is between Start and End dates

[Maha1]

I have a Master Table that include list of employees and I want to see who went on an assignment during the Date Period. I want to emphasize that not all employees went on assignment, which mean that not all employees are included in the Assignment Table and I don't want to filter them from the Master Sheet. Below is an example:

Master Table:

Assignment Table:

Master Table after merging should look like this:

[lbendlin · Accepted solution]

Assignment:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMtQ31DcyMDICMY1g7FgdoLShIYoYgm2sFBsLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, startDate = _t, endDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"startDate", type date}, {"endDate", type date}})
in
    #"Changed Type"

Master:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyVtJRMtQ31DcyADJjdRBCRphCxgghIyMjdFUwISRVhoaGKEKxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, #"Date Period" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Period", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(Assignment,(k)=>k[Employee]=[Employee] and k[startDate]<=[Date Period] and k[endDate]>=[Date Period])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"startDate", "endDate"}, {"startDate", "endDate"})
in
    #"Expanded Custom"

[Maha1]

@lbendlin Thank you for the solution. However, please recommend another solution for large data? My Master table contain 24 million rows and its been an hour and only 500k is loaded as of now.
Thanks again.

[lbendlin]

Please provide meaningful amounts of sample data.