CountIf Equivalent in Power Query , counts per row within self

I need help in creating a custom column that shows how many models per modality for each account. What would I need to input in the custom column section in power query.

1 Answer

It depends on how many other columns you have. I don't see an account column, but you mention one.

In general, in powerquery click select account and Modality columns. Right click, and use Group By. Use operation Count Rows with the new column name of your choice

Alternatively, [add aggregation] and use operation All Rows for that one

Then expand the new column using the arrows atop the new column to replace the missing data

edited answer to provide all potential combinations. Try

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"ChildName", type text}, {"Modality", type text}, {"Model Info", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"ChildName"}, { {"Modality per ChildName", each Table.RowCount(_), Int64.Type}, {"Unique Modality per ChildName", each List.Count(List.Distinct(_[Modality])), Int64.Type}, {"data", each _, type table} }), #"Expanded data" = Table.ExpandTableColumn(#"Grouped Rows", "data", {"Modality", "Model Info"}, {"Modality", "Model Info"}), #"Grouped Rows1" = Table.Group(#"Expanded data", {"ChildName", "Modality"}, { {"data", each _, type table }, {"Model Info Per Modality", each Table.RowCount(_), Int64.Type}, {"Unique Model Info Per Modality", each List.Count(List.Distinct(_[Model Info])), Int64.Type} }), #"Expanded data1" = Table.ExpandTableColumn(#"Grouped Rows1", "data", {"Modality per ChildName", "Unique Modality per ChildName", "Model Info"}, {"Modality per ChildName", "Unique Modality per ChildName", "Model Info"}) in #"Expanded data1" 

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobmxwYW6AcoSOnKaupqSes26x0K6gr5mcmru1ecinZKmnp5q%2Fbr3UnqmyZZOkwq%2FA0mannqpdp7y4edaiq6GhnmLAprjF