Return Values Only if a Unique Condition Occurs

[mmunozjr5]

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

[PhilipTreacy · Accepted solution]

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

[ThxAlot · Accepted solution]

DAX is not only more concise, but more efficient as well. Try with a dataset of more than 10k+ rows to feel the advantage.

[mmunozjr5]

Thank you so much for your solution. It worked great. I greatly appreciate your time. 🙂 

[AlienSx]

no Table.Group involved

= List.RemoveMatchingItems(
        List.Distinct(the_table[Call ID]),
        Table.SelectRows(the_table, each [Responded] = "Yes")[Call ID]
    )

[dufoq3]

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
    FilteredRows

[mmunozjr5]

Thank you so much for your solution. It worked great. I greatly appreciate your time. 🙂 

[PhilipTreacy · Accepted solution]

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