Duplicate row values based on Date field

I think I'm going to politely disagree with Pete's response here.

The information in the first table is enough to generate the 2nd table.  All we need is a column of dates for which we want the status to be evaluated.  That column can be hardcoded or generated from parameters or a list up until today, for example.  I'll leave that for the original poster to decide.

Here's my M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjTSNzbUNzIwMlCK1QFyoTxDMM9I38gCF88YRSUqz0Tf2IAIXiwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Column1", type date}}, "en-US"),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type with Locale"),
    #"Added Custom" = Table.AddColumn(#"Removed Duplicates", "QTab", each TableQ),
    #"Expanded QTab" = Table.ExpandTableColumn(#"Added Custom", "QTab", {"Employee #", "Name", "Status", "Hire Date", "Date of Leaving"}, {"QTab.Employee #", "QTab.Name", "QTab.Status", "QTab.Hire Date", "QTab.Date of Leaving"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded QTab",{{"Column1", type date}, {"QTab.Employee #", Int64.Type}, {"QTab.Name", type text}, {"QTab.Status", type text}, {"QTab.Hire Date", type date}, {"QTab.Date of Leaving", type date}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "KeepRow", each if [Column1] < [QTab.Hire Date] then 0 else 1),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([KeepRow] = 1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"KeepRow"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns1", "StatusAtDate", each if [QTab.Date of Leaving] = null or [Column1] < [QTab.Date of Leaving] then "Active" else "Inactive"),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom1",{"QTab.Status"})
in
    #"Removed Columns2"

TableQ is the first table provided with the 3 rows.

There are quite a few steps but a lot of it is housekeeping for US-style dates and getting a column of month ends to start with.

I just apply a few rules to get the status on each date.  There hasn't been any testing for for more complex scenarios, for example, firing and rehiring individuals but it does produce the 2nd table.

Obviously, it recreates from scratch each time the queries are refreshed.

Let me know what you think.