Forum Discussion
Check If ID Is In Another Table On A Daily Basis
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 |
hi Anonymous ,
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.
1 Reply
hi Anonymous ,
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.