Counting records in 1 column based on another

DaveyD

New Member
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

 ID Description OnHand SoldTo 1 ABC 10 John 1 ABC 10 Joe 2 DEF 5 3 GHI 3 Fred 4 JKL 1 5 MNO 6 Joe

<tbody>
</tbody>

How can I achieve this with power query

Thanks,
David

Last edited:

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
Group by ID
Aggregate
- Count SoldTo ==> change to Count Not Blank

 ID Description OnHand SoldTo ID Count (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
@sandy666 - Thanks a lot, that was really cool
I never used that Agregateoption before (i.e., outside of group)\

Worked perfectly

Thanks,
David

Have a nice day

alansidman

Well-known Member
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"``````