Count Occurences

[uk747]

Hi

Some sample data below although I have more columns and alot more rows
What are the steps to convert

A1​	A2​	A3​	A4​
cat​	dog​	cat​	cat​
dog​	cat​	cat​	dog​
chicken​	chicken​	dog​	dog​

Into

Option​	cat​	dog​	chicken​
A1​	1​	1​	1​
A2​	1​	1​	1​
A3​	2​	1​	0​
A4​	1​	2​	0​

I did unpivot the Data and got below but not sure how to group it so that it gives me a breakdown for each option

Attribute​	Value​
A1​	cat​
A2​	dog​
A3​	cat​
A4​	cat​
A1​	dog​
A2​	cat​
A3​	cat​
A4​	dog​
A1​	chicken​
A2​	chicken​
A3​	dog​
A4​	dog​

 

[denzo36]

just do a pivot table of your unpivoted data.

attribute in the rows, value in the columns and count of value in the values area

 

[uk747]

not sure what you mean. Initial Data is

A1	A2	A3	A4
cat	dog	cat	cat
dog	cat	cat	dog
chicken	chicken	dog	dog

SO when I create Pivot Table of above and drag A1 to A4 into rows and A1 to A4 in Values as Count I get

Row Labels​	Count of A1​	Count of A2​	Count of A3​	Count of A4​
cat	1​	1​	1​	1​
dog	1​	1​	1​	1​
cat	1​	1​	1​	1​
cat	1​	1​	1​	1​
chicken	1​	1​	1​	1​
chicken	1​	1​	1​	1​
dog	1​	1​	1​	1​
dog	1​	1​	1​	1​
dog	1​	1​	1​	1​
cat	1​	1​	1​	1​
cat	1​	1​	1​	1​
dog	1​	1​	1​	1​

 

[denzo36]

No sorry if you do a pivot table on your unpivoted data, i.e. the 2nd table in your original post. If you take that data and do a pivot table from that it should give you the required result

 

[uk747]

No sorry if you do a pivot table on your unpivoted data, i.e. the 2nd table in your original post. If you take that data and do a pivot table from that it should give you the required result

But my data is in the format of the 1st Table and I wanted to use PQ to get the 2nd Table

 

[denzo36]

Ok sorry, so you want to use PQ to do the grouping as well?

 

[JGordon11]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UnpivotColumns = Table.UnpivotOtherColumns(Source, {}, "Option", "Value"),
    GroupRows = Table.Group(UnpivotColumns, {"Option", "Value"}, {{"Count", each Table.RowCount(_)}}),
    PivotColumn = Table.Pivot(GroupRows, List.Distinct(GroupRows[Value]), "Value", "Count", each let ls = List.Sum(_) in if ls = null then 0 else ls)
in
    PivotColumn

 

[uk747]

Ok sorry, so you want to use PQ to do the grouping as well?

Yes, wanted to use PQ to do it all and convert 1st table into 2nd table

 

[uk747]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    UnpivotColumns = Table.UnpivotOtherColumns(Source, {}, "Option", "Value"),
    GroupRows = Table.Group(UnpivotColumns, {"Option", "Value"}, {{"Count", each Table.RowCount(_)}}),
    PivotColumn = Table.Pivot(GroupRows, List.Distinct(GroupRows[Value]), "Value", "Count", each let ls = List.Sum(_) in if ls = null then 0 else ls)
in
    PivotColumn

Thanks that gives result I needed

However do you have a step by step guide e.g. after getting source for next step do you select one column, unpivot other columns then manually edit formula in formula bar

Also not sure about the group rows and pivotcolumns step

 

[denzo36]

1. Import the data as a table, do NOT check the 'my table has headers'
2. Once in PQ, go to Home -> Use First Row As Headers
3.Select all columns, right click and go 'Unpivot columns'
4. Then go to Add Column -> Custom Column
5. Name column as 'count' (or whatever you want) and for the formula, just type = 1 and enter
6. This will give you a 1 for each row in your table
7. Finally, the select the column with 'cat, dog, chicken' FIRST, hold CTRL and select the column with A1, A2 etc next then go to Transform -> Pivot Column
8. From within the 'pivot columns' window, select the 'count' column as the 'values column' (i.e. the column with 1s in it that you added earlier) and in advanced options, select Sum

And voila!