Filtering for having specific values in multiple rows

[newbie2powerbie]

Hi all,

Was trying to figure out how to word this properly, I think the title gets close but an example is better. What I am trying to do is get a list of customers that have ordered 2 specific items on the same day using Power Query to filter out the rows that don't fit the conditions. Here's an example table:

Date	Customer Name	Item Ordered
2023-11-06	John	Hat
2023-11-06	John	Shoes
2023-11-06	Michael	Hat
2023-11-06	Michael	Shirt
2023-11-06	Chris	Shoes
2023-11-05	Michael	Hat
2023-11-05	Michael	Shoes
2023-11-05	Chris	Shirt
2023-11-05	John	Shoes
2023-11-04	John	Hat
2023-11-04	Chris	Shoes
2023-11-04	Chris	Hat
2023-11-04	Michael	Shirt

What am I hoping to be able to do is end up with a list of customers that ordered a hat and shoes on the same day (not hat and shirt, or not hat one day and shoes another day), so John on 2023-11-06, Michael on 2023-11-05, and Chris on 2023-11-04. Is that something that can be done with Power Query?

Thanks

[slorin]

Hi,

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtY1NNQ1MFPSUfLKz8gDUh6JJUqxOtjlgjPyU4sxZX0zkzMSU3NwaUZIB2dkFmFR4JxRlFmM3XhT/MabohmPVT/CeAzbTfH6zQRPqJjgdzeyNFa9GIESCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Customer Name" = _t, #"Item Ordered" = _t]),
#"Select Item" = Table.SelectRows(Source, each [Item Ordered] = "Hat" or [Item Ordered] = "Shoes"),
Distinct = Table.Distinct(#"Select Item"),
Group = Table.Group(Distinct, {"Date", "Customer Name"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
#"Count = 2" = Table.SelectRows(Group, each [Count] = 2)
in
#"Count = 2"

Stéphane 

[ThxAlot]

DAX is a way much better solution to such a question in terms of conciseness, flexibility or interactivity.