I have a data set with the following structure where each row represents a unique Customer x Product combination:

Customer Product Code Product category
A 1 Food
A 3 Beverages
B 1 Food
C 2 Food
C 3 Beverages
C 1 Food
D 4 Beverages
D 5 Other
E 1 Food

For each product category (Food, Beverages, Other) I need to calculate the distinct count of products in the data set.
So for this particular example the output will be:

Category Distinct count
Food 2
Beverages 2
Other 1

For this small example it's fairly easy to calculate this. However, my real data set has more than 20 thousand lines, with thousands of products and customers and hundreds of product categories. Ofcourse, I can easily add the table to a data model and then use the Distinct count functionality in a pivot table. However, I also have a lot of these tables, so converting them all into pivot tables makes it cumbersome + if anything in the data changes the pivot table has to be updated etc. So I'm looking for a formula that I can easily apply to the different tables to calculate this, but I can't wrap my head around how to do this...

Any thoughts?