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])

Solution 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

Solution 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 :)

Solution 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.

Solution 4

Look at this example: I have a table with 4 column (Serial, Team, Name, Role) as shown in here and want to create a summary table with columns (Team, HasAdmin?, CountOfMembers)

I do it in 3 step, as shown:

Step 1: add 2 new column using conditional column button in Add Column Ribbon, as shown in here and here

Step 2: change the type of new columns to "whole Number" as shown here

Step 3: Select the [Team] column and click on Group By button in Home Ribbon, and fill as shown here

finish!

for "sumif" you can put the cells value instead of "1" in step 1

Solution 5

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"

View more solutions

Comments

[Jay Killeen]

If you figure out SUMIFS please let me know

[Vasim]

Group By was helpful. Thanks!

[Jing He]

What is this "Source"?

[Diogo]

Source is the "previous step", where you can find the Column 1. I do not recommend this formula for large data entries.