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"

Answer 5

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

Answer 6

Powerquery follows more ETL approach. So you can't have an active OLE in case of powerquery. You can link you existing table in excel as datasource and can generate some specific summary for what you want. If you want to achieve the a similar functionality like excel , there are some prerequisites.

Your data should be inside an excel table. Make another table containing your search criteria. Now you need to make two table, one for datasoure , second one for your search criteria

now in the table fetched from your existing data , you can create an additional indicator column where you can ouput based on search criteria as you need something like to implement countifs if Text.Contains(col1,search criteria1) and Text.Contains(col2,searchcriteria 2) than 1 else 0 to implement sumifs if Text.Contains(col1,search criteria1) and Text.Contains(col2,searchcriteria 2) than [sumcol] else 0 summarize the table based on this column and just select the output range on the sheet for the output result(which will be another single cell table)

if you are already fetching data from other source , just create parameter table for search criteria, if your search criteria is fixed just skip data and search criteria table and use indicator column.

Unfortunately its an process and no shortcut or formula.