How to Do Countifs/Sumifs in PowerQuery M.Language Formulas?

powerbi powerquery m
Behnam2016 picture Behnam2016 · Feb 11, 2016 · Viewed 31.3k times · Source

Is it possible to Do Excel's Countifs/Sumifs formulas in powerquery Formulas? Let's Say I have [Column1] as "Criteria range" and [Column2] as "Criteria" (Example criteria [Column2] text contains "Product") and I want Count/Sum [Column1] Cells in the new custom column ([Column3])

Answer

ImkeF picture ImkeF · Feb 12, 2016

You might want to try this formula:

List.Count(List.FindText(Source[Column1], [Column2]))

it's not as flexible as SUMIFs but counts the number of records in Column1 that contain the word that stands in the current row of Column2

Related questions

What's the difference between DAX and Power Query (or M)?

I have been working on Power BI for a while now and I often get confused when I browse through help topics of it. They often refer to the functions and formulas being used as DAX functions or Power Query, …

Read More
Select row with MAX value per category Power BI

How to select row with max value per category in M of Power BI. Suppose we have table: +----------+-------+------------+ | Category | Value | Date | +----------+-------+------------+ | apples | 1 | 2018-07-01 | | apples | 2 | 2018-07-02 | | apples | 3 | 2018-07-03 | | bananas | 7 | 2018-07-04 | | bananas | 8 | 2018…

Read More
How to Find the Most Current Date From a Column in Power Query - MAX()

This is for a Power Query: I am working on a report that compiles information from different dates and I need a column that generates the most recent date in the list and the previous date to the most current …

Read More