Count occurrences from grouped Table column

[KelvinMorel]

Hi all,

I have a large table of all customer's invoices, in "AANTAL DAGEN" column I count how many days are between invoice's expire date and invoice's paid date, from there I would like to extract the number of moths the contract were paid on time.

Ex.: Contract X was paid 7 times (feb, mar, may, jun, aug, oct, nov) on time.

Following query is a test from the previews large table.

So, I grouped the list by contracts, now I would like to count how many times "AANTAL DAGEN" column is equal to 0 from a grouped column, is this even possible? Or how should I handle this case?

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"VERVALDATUM"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"KLANT", "NAAM", "CONTRACT"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [BU=number, KLANT=text, NAAM=text, CONTRACT=text, FACTUUR=text, MONTH=text, AANTAL DAGEN=number]}})
in
    #"Grouped Rows"

Thx

[KelvinMorel]

Great,

precisely what I was looking for, thank you very much.

[jgeddes · Accepted solution]

Something like the following might work for you...

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Removed Columns" = Table.RemoveColumns(Source,{"VERVALDATUM"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"KLANT", "NAAM", "CONTRACT"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [BU=number, KLANT=text, NAAM=text, CONTRACT=text, FACTUUR=text, MONTH=text, AANTAL DAGEN=number]}}),
#"Added Column" = Table.AddColumn(#"Grouped Rows", "Zeros Row Count", each Table.RowCount(Table.SelectRows([All], each [AANTAL DAGEN] = 0)))
in
    #"Added Column"