Forum Discussion
Return Values Only if a Unique Condition Occurs
I have a table that contains these 3 columns. I am trying to create a reference table in Power Query based on this table that would only bring in the Call IDs, only if the same call ID number has ALL values of No in the Responded column. If any of the values is yes, then it should ignore it.
Context: A page call is broadcasted to a number of Technicians. They all receive the same message with the same Call ID number. Once one of the technicians answers the call, then it gets assigned a value of Yes in the Responded column. The technicians that didn't answer the call received a value of No in the Responded column. Sometimes no one responds to the call and all Technicians receive a value of No in the Responded column for that Call ID. The goal of this exercise is to identify only the CAll IDs that were not answered at all, in other words, that have the value of No in all associated rows for that same CAll ID. Thank you for your assistance!
In this example, only IDs 232058 and 259931 should be returned.
| Call ID | Recipient | Responded |
| 232058 | J. Smith | No |
| 232058 | A. Collins | No |
| 232058 | S. Norton | No |
| 248830 | B. Smart | Yes |
| 256034 | S. Lonis | No |
| 256034 | K. Curtis | Yes |
| 287712 | W. Smith | No |
| 287712 | F. Roberts | No |
| 287712 | J. Cancio | Yes |
| 259931 | K. Dansel | No |
| 259931 | P. Yun | No |
Hi mmunozjr5
Download PBIX file with example below
Not sure what you mean by a reference table - please supply an example of the exact result you want. Do you simply want a column with 2 numbers in it (in this case)?
If so you can do it like this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjI2MjC1UNJR8tJTCM7NLMkAMv3ylWJ1kKQc9RSc83NyMvOKsUgG6yn45ReV5OchyZlYWBgbAPlOIDMTi0qAzMjUYoicqZmBsQlEn09+XiaykTApb6B9pUUlYDm4Pgtzc0MjoEA4pjthUm56CkH5SalFJcVYJIH+c07MS87MR3GMpaWxIcRGl8S84tQcZNdA5QL0FCJLYb6LBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Call ID" = _t, Recipient = _t, Responded = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Call ID", Int64.Type}, {"Recipient", type text}, {"Responded", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Call ID"}, {{"Count", each _, type table [Call ID=nullable number, Recipient=nullable text, Responded=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each if List.Contains([Count][Responded], "Yes") then 0 else 1), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Count", "Custom"}) in #"Removed Columns"- Group by the Call ID, keep all rows
- Create a column by checking the Responded column in the resultant table for any occurrence of "yes". Mark "yes" as 0 else 1
- Remove any rows with 0
Regards
Phil
DAX is not only more concise, but more efficient as well. Try with a dataset of more than 10k+ rows to feel the advantage.
6 Replies
Hi mmunozjr5
Download PBIX file with example below
Not sure what you mean by a reference table - please supply an example of the exact result you want. Do you simply want a column with 2 numbers in it (in this case)?
If so you can do it like this
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjI2MjC1UNJR8tJTCM7NLMkAMv3ylWJ1kKQc9RSc83NyMvOKsUgG6yn45ReV5OchyZlYWBgbAPlOIDMTi0qAzMjUYoicqZmBsQlEn09+XiaykTApb6B9pUUlYDm4Pgtzc0MjoEA4pjthUm56CkH5SalFJcVYJIH+c07MS87MR3GMpaWxIcRGl8S84tQcZNdA5QL0FCJLYb6LBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Call ID" = _t, Recipient = _t, Responded = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Call ID", Int64.Type}, {"Recipient", type text}, {"Responded", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Call ID"}, {{"Count", each _, type table [Call ID=nullable number, Recipient=nullable text, Responded=nullable text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each if List.Contains([Count][Responded], "Yes") then 0 else 1), #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = 1)), #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Count", "Custom"}) in #"Removed Columns"- Group by the Call ID, keep all rows
- Create a column by checking the Responded column in the resultant table for any occurrence of "yes". Mark "yes" as 0 else 1
- Remove any rows with 0
Regards
Phil
Hi mmunozjr5, same approach as PhilipTreacy, but this query is checking "Yes" directly in Table.Group.
Restult:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjI2MjC1UNJR8tJTCM7NLMkAMv3ylWJ1kKQc9RSc83NyMvOKsUgG6yn45ReV5OchyZlYWBgbAPlOIDMTi0qAzMjUYoicqZmBsQlEn09+XiaykTApb6B9pUUlYDm4Pgtzc0MjoEA4pjthUm56CkH5SalFJcVYJIH+c07MS87MR3GMpaWxIcRGl8S84tQcZNdA5QL0FCJLYb6LBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Call ID" = _t, Recipient = _t, Responded = _t]), GroupedRows = Table.Group(Source, {"Call ID"}, {{"Check", each List.Contains(_[Responded], "yes", Comparer.OrdinalIgnoreCase), type logical}}), FilteredRows = Table.SelectRows(GroupedRows, each [Check] = false) in FilteredRowsThank you so much for your solution. It worked great. I greatly appreciate your time. 🙂
no Table.Group involved
= List.RemoveMatchingItems( List.Distinct(the_table[Call ID]), Table.SelectRows(the_table, each [Responded] = "Yes")[Call ID] )DAX is not only more concise, but more efficient as well. Try with a dataset of more than 10k+ rows to feel the advantage.
Thank you so much for your solution. It worked great. I greatly appreciate your time. 🙂
