Translating a Countifs formula into M Language

[Anonymous]

Hi All - 

I am super new at power queries and especially the M language. I have a power queary that consolidates a bunch of tables together and I need to count how many entries per person are within a 60 day time period. If this were a normal table I would simply add a column at the end of the table and insert the formula below: 

=Countifs([Date of Review],">="&[@[Date of Review -60]], [Date of Review],"<="&[@[Date of Review]],[[Name]],[@[Name]])

I am trying to add a column within the table that would output the number of entries with these conditions. 

Please help! 

[Jimmy801]

Hello @Anonymous 

if you need this as a consolidated table in Power Query you can first filter your table for the last 60 days and then group it by user name or name or whatever and apply the function to count rows.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[edhans]

You can do this with the Table.RowCount with a nested Table.SelectRows, but it will be very slow over large data sets. Power Query is NOT optimized to so massive table scans like this. If your main table has 10,000 records and your log table has 100,000 records, it will need to do 10,000 SelectRows calls.

DAX is really the most efficient place for this.

However, if you want me to give you some variants on that code above, please provide sample data per the links below.

But DAX is hte best way to go, or have your source system, like SQL Server or SSAS do it.  I love Power Query, but use the right tool for the job here, and DAX is king on this kind of thing.How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.