Compare tables and set a text to values?

[FernandoCandido]

I have monthly tables with employees. The IDs just surges and vanish from it, as they are hired and fired. But there is no indication for that.

Can I compare the tables, they are all in one folder, and check wich one is gone and wich one is new? and mark it as new or fired?

I have no idea on how to right it in M language.

Can somebody help me?

Basically I get:

date	ID
01/01/2019	157
01/01/2019	199
01/01/2019	201
01/01/2019	202
01/01/2019	205
01/01/2019	333
01/01/2019	349
01/01/2019	357
01/01/2019	358

date	ID
01/02/2020	199
01/02/2020	201
01/02/2020	202
01/02/2020	333
01/02/2020	349
01/02/2020	350
01/02/2020	357
01/02/2020	400
01/02/2020	401

[dufoq3 · Accepted solution]

Hi @FernandoCandido, check this:

Output

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjA0NLBSUdJUNTc6VYHQxRS0ssokAaq6gRVlFTLKLGxsbYRE2w2WaM1WXGphZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, ID = _t]),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc7BCQAgDEPRXXoWTNOKOIu4/xri1eb6+ITsbfAOdoKwZr6WnfYh4QpZMSIEptiMAYWzYkKU+S6dCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, ID = _t]),
    MergedQueries = Table.NestedJoin(Table2, {"ID"}, Table1, {"ID"}, "Table1", JoinKind.FullOuter),
    ExpandedTable1 = Table.ExpandTableColumn(MergedQueries, "Table1", {"ID"}, {"Table1.ID"}),
    ReplaceValueID = Table.ReplaceValue(ExpandedTable1,
        each [ID] = null,
        each [#"Table1.ID"],
        (x,y,z)=> if y then z else x,
        {"ID"} ),
    Ad_Status = Table.AddColumn(ReplaceValueID, "Status", each if [date] = null then "Fired" else if [Table1.ID] = null then "New" else "No change", type text),
    RemovedColumns = Table.RemoveColumns(Ad_Status,{"Table1.ID"})
in
    RemovedColumns

[FernandoCandido]

Great!! Thanks. I will try it asap!!