DistinctCount based on another column

[FanofExcel18]

I have 2 separate columns in the same table. I would like to have the Distinct Count of Case Owners.

The result from below should be 3: John Smith, Jane Doe, Richard Blank

Field	New Value
Case Owner	John Smith
Status	Active
Status	Pending
Case Owner	Jane Doe
Case Owner	John Smith
Type	Invoice
Case Owner	Richard Blank

 

[Momentman]

If you have Office 365, then this should be easy with the dynamic arrays

Book1
	A	B	C	D
1	Field	New Value
2	Case Owner	John Smith		3
3	Status	Active
4	Status	Pending
5	Case Owner	Jane Doe
6	Case Owner	John Smith
7	Type	Invoice
8	Case Owner	Richard Blank
Sheet11

Cell Formulas
Range	Formula
D2	D2	=COUNTA(UNIQUE(FILTER($B$2:$B$8,$A$2:$A$8="Case Owner")))

 

[shaowu459]

FYI：

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    res = Table.Group(
                      Source,
                      "Field",
                      {"n",each
                                let a=List.Distinct(_[New Value])
                                in
                                    Text.From(List.Count(a))&":"&Text.Combine(a,",")
                      }
                     )
in
    res

 

[shaowu459]

If you only want to get "3: John Smith, Jane Doe, Richard Blank", you could try below code:

= let a=List.Distinct(Table.SelectRows(Source,each [Field]="Case Owner")[New Value]) in Text.From(List.Count(a))&":"&Text.Combine(a,",")

 

[Momentman]

If you have Office 365, then this should be easy with the dynamic arrays

Book1
	A	B	C	D
1	Field	New Value
2	Case Owner	John Smith		3
3	Status	Active
4	Status	Pending
5	Case Owner	Jane Doe
6	Case Owner	John Smith
7	Type	Invoice
8	Case Owner	Richard Blank
Sheet11

Cell Formulas
Range	Formula
D2	D2	=COUNTA(UNIQUE(FILTER($B$2:$B$8,$A$2:$A$8="Case Owner")))

Didn't realise it was a PQ question