• We are receiving reports of members using the private messaging service (Conversations) in ways that break the forum rules:
    • Do not invite another member to take the question off the forum (i.e. do not suggest that they post to a different forum, do not suggest that they email or private message you the problem, do not simply post a link to another forum, unless it is to a specific, relevant, thread). This applies equally to members asking or answering questions.
    • Soliciting business for yourself is not permitted. This is an all volunteer board, so offering solutions in exchange for compensation is not permitted. Likewise, members seeking solutions must not offer compensation for them. If you have an urgent need, check the Consulting Services page.
    Please help us out by clicking the Report link on any messages you receive that violate these rules. Thank you
  • If you would like to post, please check out the MrExcel Message Board FAQ and click here to register.
    If you forgot your password, you can reset your password.

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,241
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,034
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,077,961
Messages
5,337,432
Members
399,145
Latest member
SPLhorses

Some videos you may like

This Week's Hot Topics

Top