Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Power Query Editor Count of Unique Column Values Into Two Columns

I have two columns in a Table as seen below.

Store NumberAudit Violation
8297Failed Audit
8297Passed Audit
8297Failed Audit
8297Passed Audit
8297Passed Audit
8291Failed Audit
8291Passed Audit
8291Passed Audit
8281Passed Audit
8267Failed Audit
8267Passed 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 NumberCount Failed AuditsCount Passed Audits
829723
829112
828101
826711
Need Help
  • Payeras_BI's avatar
    Payeras_BI
    5 years ago

    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}})



2 Replies

  • Payeras_BI's avatar
    Payeras_BI
    Solution Sage
    5 years ago

    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}})