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,311
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,044
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,444
Messages
5,340,317
Members
399,367
Latest member
ahmed elsaid

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top