Results 1 to 7 of 7

Thread: Counting records in 1 column based on another

  1. #1
    New Member
    Join Date
    May 2015
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Counting records in 1 column based on another

    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

    How can I achieve this with power query

    Thanks,
    David
    Last edited by DaveyD; Aug 14th, 2019 at 08:23 PM.

  2. #2
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    3,042
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Counting records in 1 column based on another

    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:
    // 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
    Last edited by sandy666; Aug 14th, 2019 at 09:03 PM.
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  3. #3
    New Member
    Join Date
    May 2015
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting records in 1 column based on another

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

    Worked perfectly

    Thanks,
    David

  4. #4
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    3,042
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Counting records in 1 column based on another

    Glad to help

    Have a nice day
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  5. #5
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    5,008
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting records in 1 column based on another

    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"
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  6. #6
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    3,042
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Counting records in 1 column based on another

    Quote Originally Posted by DaveyD View Post
    outside of group
    this is not outside

    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    I don't use vba in any form!

  7. #7
    New Member
    Join Date
    May 2015
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting records in 1 column based on another

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •