Counting records in 1 column based on another

[DaveyD]

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

[TABLE="width: 350"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Description[/TD]
[TD]OnHand[/TD]
[TD]SoldTo[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ABC[/TD]
[TD]10[/TD]
[TD]John[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ABC[/TD]
[TD]10[/TD]
[TD]Joe[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]DEF[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]GHI[/TD]
[TD]3[/TD]
[TD]Fred[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]JKL[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]MNO[/TD]
[TD]6[/TD]
[TD]Joe[/TD]
[/TR]
</tbody>[/TABLE]

How can I achieve this with power query

Thanks,
David

 

[sandy666]

Group by ID
Aggregate
- Count SoldTo ==> change to Count Not Blank

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]ID[/td][td=bgcolor:#5B9BD5]Description[/td][td=bgcolor:#5B9BD5]OnHand[/td][td=bgcolor:#5B9BD5]SoldTo[/td][td][/td][td=bgcolor:#70AD47]ID[/td][td=bgcolor:#70AD47]Count (Not Blank) of SoldTo[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]ABC[/td][td=bgcolor:#DDEBF7]

10​

[/td][td=bgcolor:#DDEBF7]John[/td][td][/td][td=bgcolor:#E2EFDA]

1​

[/td][td=bgcolor:#E2EFDA]

2​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

1​

[/td][td]ABC[/td][td]

10​

[/td][td]Joe[/td][td][/td][td]

2​

[/td][td]

0​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

2​

[/td][td=bgcolor:#DDEBF7]DEF[/td][td=bgcolor:#DDEBF7]

5​

[/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#E2EFDA]

3​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

3​

[/td][td]GHI[/td][td]

3​

[/td][td]Fred[/td][td][/td][td]

4​

[/td][td]

0​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

4​

[/td][td=bgcolor:#DDEBF7]JKL[/td][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#E2EFDA]

5​

[/td][td=bgcolor:#E2EFDA]

1​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

5​

[/td][td]MNO[/td][td]

6​

[/td][td]Joe[/td][td][/td][td][/td][td][/td][/tr]
[/table]

[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]

 

[DaveyD]

@sandy666 - Thanks a lot, that was really cool
I never used that Agregateoption before (i.e., outside of group)\

Worked perfectly

Thanks,
David

 

[sandy666]

Glad to help

Have a nice day

 

[alansidman]

Another option. Not as simple as Sandy but still works

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"

 

[sandy666]

outside of group

this is not outside

 

[DaveyD]

@alansidman - nice one - thanks!
@sandy666 - got it, thanks