PIVOT Count Only " - " in table

[surkdidat]

Is there anyway of creating a Pivot to count the number of occurrences of "-" when the columns and rows match?

I know I can do this by INDEX MATCH MATCH (or similar) but would prefer it in a pivot, that way I can get asy access on the data when clicking on the info?

 

[sandy666]

any example of source for your "pivot" ?

 

[surkdidat]

sss

any example of source for your "pivot" ?

 

[sandy666]

Wondeful example, really but where are the data?

 

[surkdidat]

so I want to create a Pivot with Names in the rows, and the categories as columns

Name	Contacted	Appointment	Sale
Claire	1	2	1
Dan		1

ID	Name	Date	Contacted?	Appointment	Sale
111	Adam	02/06/2019	Yes	Yes	Yes
222	Ben	01/06/2019	Yes	Yes	Yes
333	Claire	02/06/2019	Yes	-	-
444	Dan	02/06/2019	Yes	-	Yes
555	Claire	02/06/2019	-	-	Yes

In this Pivot I want it to say

any example of source for your "pivot" ?

 

[sandy666]

something like this?

ID	Name	Date	Contacted?	Appointment	Sale		Name	Value	Appointment	Sale	Contacted?
111​	Adam	02/06/2019​	Yes	Yes	Yes		Claire	-	2​	1​	1​
222​	Ben	01/06/2019​	Yes	Yes	Yes		Dan	-	1​
333​	Claire	02/06/2019​	Yes	-	-
444​	Dan	02/06/2019​	Yes	-	Yes
555​	Claire	02/06/2019​	-	-	Yes

 

[surkdidat]

Yes, please, thank you

something like this?

ID	Name	Date	Contacted?	Appointment	Sale		Name	Value	Appointment	Sale	Contacted?
111​	Adam	02/06/2019​	Yes	Yes	Yes		Claire	-	2​	1​	1​
222​	Ben	01/06/2019​	Yes	Yes	Yes		Dan	-	1​
333​	Claire	02/06/2019​	Yes	-	-
444​	Dan	02/06/2019​	Yes	-	Yes
555​	Claire	02/06/2019​	-	-	Yes

 

[sandy666]

so use PowerQuery (Get&Transform)

[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Name", type text}, {"Date", type date}, {"Contacted?", type text}, {"Appointment", type text}, {"Sale", type text}}),
    Unpivot = Table.UnpivotOtherColumns(Type, {"ID", "Name", "Date"}, "Attribute", "Value"),
    Filter = Table.SelectRows(Unpivot, each ([Value] = "-")),
    Group = Table.Group(Filter, {"Name", "Attribute", "Value"}, {{"Count", each Table.RowCount(_), type number}}),
    Pivot = Table.Pivot(Group, List.Distinct(Group[Attribute]), "Attribute", "Count")
in
    Pivot[/SIZE]