cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mumair Regular Visitor
Regular Visitor

How to Count

Wondering how to do something as follows:

 

Order # | Error

Order 1 | Something

Order 1 | Something else

Order 2 | null

Order 3 | Something

 

I want a column or a table that says something like this:

Order # | Error Count

Order 1 | 2

Order 2 | 0

Order 3 | 1

 

Any ideas?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: How to Count

Like this:

 

Specializing in Power Query Formula Language (M)
6 REPLIES 6
Super User
Super User

Re: How to Count

You can use "Group By" in the query editor.

In order to create base code, you can choose to sum the "Error" column and then adjust the generated code to the code below.

 

Group By Order.png

 

let
    Source = Input,
    #"Grouped Rows" = Table.Group(Source, {"Order #"}, {{"Error Count", each List.NonNullCount([Error]), Int64.Type}})
in
    #"Grouped Rows"
Specializing in Power Query Formula Language (M)
mumair Regular Visitor
Regular Visitor

Re: How to Count

I tried to do that, but it wouldn't let me. That is because the error column is text, so it is not letting me use the sum operator

Super User
Super User

Re: How to Count

Ah, forgot to tell. Sure you will get errors at first, just ignore these and adjust the code, then you'll be fine.

Specializing in Power Query Formula Language (M)
Super User
Super User

Re: How to Count

Like this:

 

Specializing in Power Query Formula Language (M)
mumair Regular Visitor
Regular Visitor

Re: How to Count

Wow, thank you so much, this was really helpful.

 

One last question. I want to either append this column to the original table, or create a new table without altering the first table. How do I do this?

Super User
Super User

Re: How to Count

Just save & load the query with a new name, then you'll have your new table.

Specializing in Power Query Formula Language (M)