Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 |
Solved! Go to 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.
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
21 | |
20 | |
13 | |
13 |