Count Unique items based on criteria

[trenteb]

I have a set of sales data by date by location and by customer. I want to be able to count the customers who have purchased by month by location, whether they have purchased multiple times during the month or just once doesn't matter they should only be counted once as trying to ascertain the number of active customers.

Note - not able to post mini sheet, not being provided with option?

 

[alansidman]

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}}),
    #"Extracted Month Name" = Table.TransformColumns(#"Changed Type", {{"Month", each Date.MonthName(_), type text}}),
    #"Grouped Rows" = Table.Group(#"Extracted Month Name", {"Month", "Sales Office", "Category", "Customer Name"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Count"})
in
    #"Removed Columns"

Book7
	F	G	H	I
1	Month	Sales Office	Category	Customer Name
2	November	ROC	Black	Customer A
3	November	ABC	Green	Customer C
4	November	SHE	Green	Customer E
5	November	SHE	Black	Customer F
6	October	ROC	Yellow	Customer A
7	October	ABC	Black	Customer C
8	October	ABC	Green	Customer D
9	October	SHE	Yellow	Customer E
10	October	SHE	Yellow	Customer F
11	October	SHE	Green	Customer F
12	September	ROC	Black	Customer A
13	September	ROC	Yellow	Customer A
14	September	ROC	Yellow	Customer B
15	September	ABC	Yellow	Customer C
16	September	ABC	Green	Customer C
17	September	SHE	Green	Customer E
18	September	SHE	Black	Customer E
19	August	ROC	Black	Customer B
20	August	ABC	Black	Customer C
21	August	ABC	Green	Customer D
22	August	SHE	Green	Customer E
23	August	SHE	Yellow	Customer E
24	August	SHE	Yellow	Customer F
25	July	ABC	Green	Customer C
26	July	ABC	Yellow	Customer D
27	July	SHE	Yellow	Customer G
28	July	SHE	Black	Customer F
29	July	SHE	Green	Customer F
30	July	SHE	Green	Customer E
Sheet2