Counting records in 1 column based on another

DaveyD

New Member
Joined
May 20, 2015
Messages
27
In the table below, I need to get a unique list of IDs with a count of how many were sold
In the example below, ID #1 was sold twice, #2 zero, #3 one, #4 zero, #5 one.

In order to do this, I have to group it by ID and aggregate with count
But doing so results in 2 for #1 , 1 for #2 (instead of zero), 1 for #3 etc.

In the final result, I do not need the "SoldTo" column, but I do need the Description and OnHand

IDDescriptionOnHandSoldTo
1ABC10John
1ABC10Joe
2DEF5
3GHI3Fred
4JKL1
5MNO6Joe

<tbody>
</tbody>

How can I achieve this with power query

Thanks,
David
 
Last edited:

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,722
Group by ID
Aggregate
- Count SoldTo ==> change to Count Not Blank

IDDescriptionOnHandSoldToIDCount (Not Blank) of SoldTo
1​
ABC
10​
John
1​
2​
1​
ABC
10​
Joe
2​
0​
2​
DEF
5​
3​
1​
3​
GHI
3​
Fred
4​
0​
4​
JKL
1​
5​
1​
5​
MNO
6​
Joe

Code:
[SIZE=1]// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Group = Table.Group(Source, {"ID"}, {{"Count", each _, type table}}),
    Aggregate = Table.AggregateTableColumn(Group, "Count", {{"SoldTo", List.NonNullCount, "Count (Not Blank) of SoldTo"}})
in
    Aggregate[/SIZE]
 
Last edited:

DaveyD

New Member
Joined
May 20, 2015
Messages
27
@sandy666 - Thanks a lot, that was really cool
I never used that Agregateoption before (i.e., outside of group)\

Worked perfectly

Thanks,
David
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,193
Office Version
2019
Platform
Windows
Another option. Not as simple as Sandy but still works

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Description", type text}, {"OnHand", Int64.Type}, {"SoldTo", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if([SoldTo]<>null) then 1 else 0),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Custom", "ID", "Description", "OnHand"}, {{"Count", each Table.RowCount(_), type number}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Sales", each if([Custom]=0) then 0 else [Count]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Count"})
in
    #"Removed Columns"
 

Watch MrExcel Video

Forum statistics

Threads
1,090,186
Messages
5,412,951
Members
403,457
Latest member
mohammad1335

This Week's Hot Topics

Top