Power Query Editor Count of Unique Column Values Into Two Columns

[Anonymous]

I have two columns in a Table as seen below.

Store Number	Audit Violation
8297	Failed Audit
8297	Passed Audit
8297	Failed Audit
8297	Passed Audit
8297	Passed Audit
8291	Failed Audit
8291	Passed Audit
8291	Passed Audit
8281	Passed Audit
8267	Failed Audit
8267	Passed Audit

I need to basically get a count of each unqieu value and put them into two separate columns like below. How can I do this in Power Query Editor?

Store Number	Count Failed Audits	Count Passed Audits
8297	2	3
8291	1	2
8281	0	1
8267	1	1

[Payeras_BI · Accepted solution]

Hi @Anonymous ,

In case you still want to do it in PQ Editor.

= Table.Group(#"Changed Type", {"Store Number"},
{{"Count Failed Audits", each Table.RowCount(Table.SelectRows(_, each ([Audit Violation] = "Failed Audit"))), Int64.Type},
{"Count Passed Audits", each Table.RowCount(Table.SelectRows(_, each ([Audit Violation] = "Passed Audit"))), Int64.Type}})

[amitchandak]

@Anonymous , to me this seems like unpivot with count.

First, create a combined column

Audit Violation = [Audit] & " " & [Violation]

using this column unpivot and take count

https://radacad.com/pivot-and-unpivot-with-power-bi