如何在PowerQuery M语言公式中使用Countifs/Sumifs函数？

Question

在PowerQuery公式中能否使用Excel的Countifs/Sumifs公式？假设我有[列1]作为“条件范围”，[列2]作为“条件”（例如条件[列2]文本包含“产品”），我希望计算/求和新自定义列([列3])中[列1]单元格的数量。

- Behnam2016

Answer 1

你可以尝试使用这个公式：

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

它不像SUMIFs那样灵活，但是可以计算Column1中包含Column2当前行的单词的记录数。

- ImkeF

---

这个“Source”是什么？ - Jing He

“Source” 是“前一步”，在那里您可以找到“列1”。我不建议在大数据输入中使用此公式。 - Diogo

Answer 2

我在Power Query Editor中使用这个函数来进行COUNTIF，但我还没有掌握COUNTIFS或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

我接下来会用它这样：

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

我已经在我的博客上详细介绍了这个问题，这是我偏爱的解决方法。 http://jaykilleen.com/posts/countif-in-power-query-or-powerbi-using-m-language 希望这能帮到你 :)

- Jay Killeen

---

如果你弄清楚了SUMIFS，请告诉我。 - Jay Killeen

Answer 3

你可以使用筛选器根据条件进行筛选，然后在Transform选项卡中使用“计数行”功能。筛选可以通过单击列中的箭头来完成。

另外，你也可以筛选列，然后使用“分组”功能来计算总数或求和。

- Alejandro Lopez-Lago - MSFT

---

Group By很有帮助。谢谢！ - Vasim

Answer 4

我认为我解决了这个问题。

我创建了一个 csv 文件 [Sumifs test.csv]，并将查询链接到该文件。

这是源表格，看起来像这样： [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}

让
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}}),

第一阶段=#"更改类型" ,
#"分组行" = Table.Group(#"更改类型", {"Alpha1", "Alpha1_1"}, {{"Values", each List.Sum([Value]), type number}}),

MyList = List.Buffer(#"分组行"[Values]),
Custom1 = #"分组行",

#"添加自定义" = Table.AddColumn(Custom1, "Acumulative", each List.Sum( List.Select(MyList, (x) => x>=[Values]))),

#"分组行1" = Table.Group(#"添加自定义", {"Alpha1"}, {{"Values2", each List.Sum([Values]), type number}}),

MyList2 = List.Buffer(#"分组行1"[Values2]),
Custom2 = #"分组行1",

#"合并查询" = Table.NestedJoin(第一阶段,{"Alpha1"},Custom2,{"Alpha1"},"NewColumn",JoinKind.LeftOuter),
#"展开新列" = Table.ExpandTableColumn(#"合并查询", "NewColumn", {"Values2"}, {"NewColumn.Values2"}), 第二阶段=#"展开新列",

#"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"

- Tamas

Answer 5

看这个例子：我有一个包含4列（序号，团队，名称，角色）的表格，如此处所示，我想创建一个摘要表格，其列为（团队，是否有管理员？，成员数量）

我通过以下3步完成：

步骤1：使用条件列按钮在添加列选项卡中添加2个新列，如此处和此处所示

步骤2：将新列的类型更改为“整数”，如此处所示

步骤3：选择[团队]列，单击分组按钮，在主页选项卡中填写如此处所示

完成！

对于 "sumif" 函数，您可以在第一步中放置单元格的值而不是 "1"。

- hadi ahadi

Answer 6

Powerquery遵循更多的ETL方法。因此，在使用Powerquery时，您不能有一个活动的OLE。您可以将现有的Excel表格作为数据源链接，并生成一些特定的汇总内容。如果要实现与Excel类似的功能，则需要满足一些先决条件。

您的数据应该在Excel表格中。 创建另一个包含您的搜索条件的表格。 现在，您需要创建两个表格，一个用于数据源，另一个用于搜索条件

现在，在从现有数据中获取的表格中，您可以创建一个附加的指示器列，在其中，根据您的搜索条件输出所需的内容。例如，如果要实现countifs，则可以执行以下操作： 如果Text.Contains(col1，search criteria1) and Text.Contains(col2，searchcriteria 2)则输出1，否则输出0 要实现sumifs 如果Text.Contains(col1，search criteria1) and Text.Contains(col2，searchcriteria 2)则输出[sumcol]，否则输出0 基于此列对表格进行汇总，然后在表格上选择输出范围（这将是另一个单元格表格）的输出结果。

如果您已经从其他来源提取数据，只需为搜索条件创建参数表格，如果您的搜索条件是固定的，请跳过数据和搜索条件表格并使用指示器列。

不幸的是，这是一个过程，没有捷径或公式。

- S M