Counting records in 1 column based on another

DaveyD

New Member
Joined
May 20, 2015
Messages
14
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:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,277
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
14
@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,040
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"
 

Forum statistics

Threads
1,078,134
Messages
5,338,428
Members
399,232
Latest member
stevenmoritz

Some videos you may like

This Week's Hot Topics

Top