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

Question

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 1

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

Answer 2

I use this function in my Power Query Editor for COUNTIF but I haven't yet mastered COUNTIFS or SUMIFS:

let 
  countif = (tbl as table, col as text, value as any) as number =>
    let
      select_rows = Table.SelectRows(tbl, each Record.Field(_, col) = value),
      count_rows = Table.RowCount(select_rows)
    in
      count_rows
in
    countif

I then use it like

add_column = Table.AddColumn(
  last_expression,
  "count", 
  each
    f_countif(Product, "Id",[ProductId]))

I have written this up pretty extensively on my blog and this is my preferred way of doing it.

http://jaykilleen.com/posts/countif-in-power-query-or-powerbi-using-m-language

I hope this helps you :)

Answer 3

You can use filters to filter based on your criteria and then use Count Rows in the Transform ribbon tab. Filtering can be done by clicking on the arrow in the column.

Alternatively, you can filter the column and then use Group By to compute the count or sum.

Answer 4

I think I solved this question.

I created a csv. [Sumifs test.csv] and linked the query to this file.

This is the source table, looks like this: [Header] {rows}

[Alpha1] {A A A A A A A A A}

[Alpha1] {AA AA AA AA BB BB BB BB BB}

[Num1] {1 1 1 1 1 1 1 1 1}

[Num2] {11 11 11 11 22 22 22 22 22}

[Value] {1 2 3 4 5 6 7 8 9}

let
Source = Csv.Document(File.Contents("D:\Power BI\Tests\Sumifs test.csv"),[Delimiter=",", Encoding=1252]),
#"Promoted Headers1" = Table.PromoteHeaders(Source),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Num1", Int64.Type}, {"Num2", Int64.Type}, {"Value", Int64.Type}}),

Phase1=#"Changed Type" ,
#"Grouped Rows" = Table.Group(#"Changed Type", {"Alpha1", "Alpha1_1"}, {{"Values", each List.Sum([Value]), type number}}),

MyList = List.Buffer(#"Grouped Rows"[Values]),
Custom1 = #"Grouped Rows",

#"Added Custom" = Table.AddColumn(Custom1, "Acumulative", each List.Sum( List.Select(MyList, (x) => x>=[Values]))),

#"Grouped Rows1" = Table.Group(#"Added Custom", {"Alpha1"}, {{"Values2", each List.Sum([Values]), type number}}),

MyList2 = List.Buffer(#"Grouped Rows1"[Values2]),
Custom2 = #"Grouped Rows1",

#"Merged Queries" = Table.NestedJoin(Phase1,{"Alpha1"},Custom2,{"Alpha1"},"NewColumn",JoinKind.LeftOuter),
#"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"Values2"}, {"NewColumn.Values2"}), Phase2=#"Expanded NewColumn",

#"Merged Queries2" = Table.NestedJoin(Phase2,{"Alpha1_1"},Custom1,{"Alpha1_1"},"NewColumn",JoinKind.LeftOuter),

#"Expanded NewColumn1" = Table.ExpandTableColumn(#"Merged Queries2", "NewColumn", {"Values"}, {"NewColumn.Values"})

in

#"Expanded NewColumn1"