Extracting Data that meets multiple criteria in one column for one customer ID

[MichiganWilliams]

I swear this has to be easy but I'm not figuring this out. I've looked at VLOOKUP, Index & Match, Pivot Tables, etc.

I've got a table with 65k rows. In one column, I have a customerID, in another column I have an item purchased. I have many other columns, but I want to be able to find customers that have purchased BOTH item A & item B, doesn't have to be on the same date. When I try filtering my table, I get anyone who purchased either A or B.

I tried a Pivot table and I did Rows Customer ID and column product purchased, I think I threw in another random field in the values column set to Count. It's not exactly what I want though. With 65k rows, that takes a lot of scrolling.
Imagine something like the table below, but with a ton of rows. I'd want it to return only Customer 1 because they've purchased A and they've purchased B. Is there a formula that could extract data like this when it's dependent on a customer ID in one column and multiple criteria in a separate column?

Follow up question, What if I wanted someone who has purchased BOTH A and any other product?

Customer ID	Product Purchased	Date
1	A	1/1/19
2	B	1/1/19
3	C	1/1/19
4	A	1/1/19
1	B	2/1/19
5	D	2/1/19
5	E	2/1/19
4	A	2/1/19

<tbody>
</tbody>

Thank you!

 

[sandy666]

something like this?

Customer ID	Product Purchased	Date		Customer ID	Product Purchased
1​	A	01/01/2019​		1​	A,B
2​	B	01/01/2019​		2​	B
3​	C	01/01/2019​		3​	C
4​	A	01/01/2019​		4​	A,A
1​	B	02/01/2019​		5​	D,E
5​	D	02/01/2019​
5​	E	02/01/2019​
4​	A	02/01/2019​

 

[MichiganWilliams]

I could work with that. What's the best way to get my table to do that?

 

[sandy666]

with PowerQuery aka Get&Transform and M-code below:

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"Customer ID"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "Product Purchased", each Table.Column([Count],"Product Purchased")),
    Extract = Table.TransformColumns(List, {"Product Purchased", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    Extract[/SIZE]

 

[MichiganWilliams]

Thank you very much!

 

[sandy666]

You are welcome

Have a nice day