Forum Discussion

KelvinMorel's avatar
KelvinMorel
Helper II
1 year ago
Solved

Count occurrences from grouped Table column

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

Need Help
  • jgeddes's avatar
    jgeddes
    1 year ago

    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"

2 Replies

  • jgeddes's avatar
    jgeddes
    Super User
    1 year ago

    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"