Check If ID Is In Another Table On A Daily Basis

[Eleanor24]

I have two tables:

1) Contains a list of only IDs for providers

2) Contains a list of IDs for providers who are available and the date(s) they are available

I want to check if the provider has availability on any given day. I'm trying to create a table that shows all of the providers by id (table1), and cross-reference that with the table of IDs that contain providers who are working on any given day. I just want to add a yes/no column to table1 signifying that they are or are not working, then be able to filter that using a slicer for the date.

It should be a very simple fix but I am only finding providers who are working, not both. I think it's because everything I'm doing is dependent on a date that is only found in the table of availability. Any help would be appreciated.

Table 1.

ID

1

2

3

4

Table 2.

ID	On Schedule Date
2	4/29/2024
3	4/29/2024

Desired Outcome:

ID	Working Y/N
1	N
2	Y
3	Y
4	N

[adudani · Accepted solution]

hi @Eleanor24 ,

copy and paste the below code into the advanced editor of a blank query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMgKTxmDSRCk2FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}}),
    #"Table 2 lookup" = Table.AddColumn(#"Changed Type", "Table2", each #"Table 2"[ID]),
    #"Working Y/N" = Table.AddColumn(#"Table 2 lookup", "Working Y/N", each if List.ContainsAny([Table2],{[ID]}) = true then "Y" else "N", type text),
    #"Removed Columns" = Table.RemoveColumns(#"Working Y/N",{"Table2"})
in
    #"Removed Columns"

note:

if the on schedule date has multiple dates, this is not an ideal solution it could be Y/N for schedules on any of the multiple dates.

if this is the case, kindly provide a sample input and expected output for the same.