Search code examples
excelpowerquerym

COUNTIFS Equivalent for PowerQuery in Excel

I have table which is the result of three separate queries which haven been appended. I would like to create a custom column in Power query which counts the number of say "Nein" in each row etc. enter image description here

Solution

  • Ron Rosenfeld's, answer here How to Sum N columns modified slightly does the job

    let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ColToCheck = List.RemoveFirstN(Table.ColumnNames(Source),1),
#"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
totals = Table.AddColumn( #"Added Index", "Count", each List.Count(Text.PositionOf(Text.Combine(Record.ToList(Table.SelectColumns(#"Added Index",ColToCheck){[Index]}),":")&":","Nein:",Occurrence.All ))),
#"Removed Columns" = Table.RemoveColumns(totals,{"Index"})
in #"Removed Columns"