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 ...
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.
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.