Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
KelvinMorel
Helper II
Helper II

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"

 

 

screenshot_15012025_02.jpg

 

Thx

1 ACCEPTED SOLUTION
jgeddes
Super User
Super User

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"




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

2 REPLIES 2
KelvinMorel
Helper II
Helper II

Great,

 

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

 

jgeddes
Super User
Super User

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"




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.