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])
6 Answers
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 :)
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"
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
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.