COUNT distinct by date

[jimrward]

part 2 of trying to learn Power Query
my set of data below is an attendance log, where a person can participate in an activity more than once in a day, so what I would like to do is summarise the data, by person showing total activity count and the count of individual days, so even if a person does 3 activities in a day that counts as 1 visit

CurrentData.xlsx
	A	B	C	D	E	F	G	H	I
1	Date	Year	Quarter	Activity	Name		Name	ActivityCount	Visits
2	20/03/2020	2020	1	S5 M10 T15	Bruce		Jim	20	5
3	20/03/2020	2020	1	S4 M10 T14	Bruce		Bruce	11	4
4	13/03/2020	2020	1	S5 M10 T15	Bruce		Mike	10	5
5	13/03/2020	2020	1	S5 M10 T15	Bruce
6	06/03/2020	2020	1	S9 M10 T19	Bruce
7	06/03/2020	2020	1	S4 M14 T18	Bruce
8	28/02/2020	2020	1	S5 M10 T15	Bruce
9	28/02/2020	2020	1	S3 M10 T13	Bruce
10	28/02/2020	2020	1	S4 M10 T14	Bruce
11	28/02/2020	2020	1	S6 M10 T16	Bruce
12	28/02/2020	2020	1	S3 M10 T13	Bruce
13	30/01/2023	2023	1	S5 M10 T15	Mike
14	23/01/2023	2023	1	S7 M10 T17	Mike
15	23/01/2023	2023	1	S3 M10 T13	Mike
16	16/01/2023	2023	1	S4 M10 T14	Mike
17	16/01/2023	2023	1	S3 M10 T13	Mike
18	09/01/2023	2023	1	S8 M10 T18	Mike
19	12/12/2022	2022	4	S12 M0 T12	Mike
20	12/12/2022	2022	4	S5 M10 T15	Mike
21	12/12/2022	2022	4	S3 M10 T13	Mike
22	12/12/2022	2022	4	S1 M10 T11	Mike
23	03/02/2023	2023	1	S7 M20 T27	Jim
24	03/02/2023	2023	1	S4 M20 T24	Jim
25	03/02/2023	2023	1	S5 M10 T15	Jim
26	03/02/2023	2023	1	S5 M10 T15	Jim
27	30/01/2023	2023	1	S6 M10 T16	Jim
28	30/01/2023	2023	1	S5 M10 T15	Jim
29	30/01/2023	2023	1	S5 M10 T15	Jim
30	27/01/2023	2023	1	S4 M10 T14	Jim
31	27/01/2023	2023	1	S6 M20 T26	Jim
32	27/01/2023	2023	1	S20 M10 T30	Jim
33	27/01/2023	2023	1	S7 M20 T27	Jim
34	20/01/2023	2023	1	S9 M10 T19	Jim
35	20/01/2023	2023	1	S3 M20 T23	Jim
36	20/01/2023	2023	1	S5 M20 T25	Jim
37	20/01/2023	2023	1	S9 M11 T20	Jim
38	20/01/2023	2023	1	S3 M10 T13	Jim
39	20/01/2023	2023	1	S14 M10 T24	Jim
40	13/01/2023	2023	1	S17 M20 T37	Jim
41	13/01/2023	2023	1	S4 M20 T24	Jim
42	13/01/2023	2023	1	S4 M10 T14	Jim
Sheet1

 

[JEC]

This should do

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    grp = Table.Group(Source, {"Name"}, {{"Count", each _, type table [Date=nullable datetime]}}),
    agg = Table.AggregateTableColumn(grp, "Count", {{"Date", List.Count, "ActivityCount"}, {"Date", each List.Count(List.Distinct(_)), "Visits"}})
in
    agg

 

[jimrward]

Many thanks for the quick reply I will try and disseminate it, step by step, whilst power query is powerful it ain’t half obtuse in its syntax

 

[jimrward]

@JEC once again many thanks, what would I need to add to the above to group by year and quarter

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Result = Table.Group(Source, {"Name", "Year", "Quarter"}, {{"Activity Count", each List.Count(_[Date])},{"Visits", each List.Count(List.Distinct(_[Date]))}})
in
    Result

 

[jimrward]

Thanks to both works perfectly, now applying it to my mountains of data, unfortunately the forum doesnt allow a double tick for solution so I will try an like both solutions

 

[jdellasala]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Result = Table.Group(Source, {"Name", "Year", "Quarter"}, {{"Activity Count", each List.Count(_[Date])},{"Visits", each List.Count(List.Distinct(_[Date]))}})
in
    Result

I always love your (SICK!) code, and envy your ability to use as few steps as possible. However, in this case the Result table doesn't quite fit the bill (even with removing unwanted columns):

Book1
	G	H	I
6	Name	Activity Count	Visits
7	Bruce	11	4
8	Mike	6	4
9	Mike	4	1
10	Jim	20	5
Sheet1

While not quite as efficient as yours, @JEC 's code does result in the table requested:

Book1
	K	L	M
1	Name	ActivityCount	Visits
2	Bruce	11	4
3	Mike	10	5
4	Jim	20	5
Sheet1

I only mention it because my fix was boring - just adding another Grouping by name and summing the other 2 columns, but I'm sure you'd have a more elegant solution!

 

[jimrward]

JGordon11’s code is for grouping by year and quarter
JEC’s code covers the initial remit

 

[jdellasala]

JGordon11’s code is for grouping by year and quarter
JEC’s code covers the initial remit

Not what you said you implied what you expected.

 

[Fluff]

Not what you said you implied what you expected.

Really ?

Thanks to both works perfectly

Suggests otherwise.