PowerQuery Countifs across multiple columns

[jlys89]

Hi 

I'm trying to figure out how to the do the following in PowerQuery (M) for Power BI where I want to add 2 additional columns to my data set as seen below:

What i want to achieve is counting the amount of times that a given feature is procent in the columns Feature 1 - Feature 6 where features are grouped into categories. (The 2 custom columns i need - Category 1 and Category 2)

Category 1: Feature 1, Feature 3, Feature 5

Category 2: Feature 2, Feature 4, Feature 6

For ID 1 the prefered method would be a count of: 3 because Feature 1, Feature 3 and Feature 5 is present in the 6 columns.

Afterwards I want to devide this by the total number of features meaning we get:

Category 1: 3/3 = 1,00 or 100 %

Category 2: 0/3 = 0  or 0 %

ID	Feature 1	Feature 2	Feature 3	Feature 4	Feature 5	Feature 6	Features	Category 1	Category 2
1	Feature 1	null	Feature 3	null	Feature 5	null	3	1	0
2	null	null	Feature 3	null	Feature 5	Feature 6	3	0,666666667	0,33
3	null	null	Feature 3	null	Feature 5	Feature 6	3	0,666666667	0,33
4	null	Feature 2	null	Feature 4	null	null	2	0	0,67
5	null	null	null	Feature 4	null	null	1	0	0,33

Any help would be much appreciated 🙂

[jlys89 · Accepted solution]

Thanks. I ended up finding this article that solved the problem 🙂 Thanks alot for the contributions though 🙂

https://community.powerbi.com/t5/Power-Query/Multiple-CountIf-function-in-Power-Query/m-p/1321475

[mahoneypat]

This is doable in query, but is much better done by unpivoting your data and then using a DAX measure.

For example, here is a formula that will generate your Category 1 column in the query editor.  Put it in a custom column.

= let thisrecordlist = Record.ToList(_) in List.Count(List.Select(List.Transform({1,3,5}, each thisrecordlist{_}), each Text.Contains(_, "Feature")))/3

Regards,

Pat

[jlys89]

Hi Pat,
Thanks alot of taking the time. Any chance you can explain how this part functions (List.Transform({1,3,5} I'm currently getting an error when utilizing your formula and I think it might be due to these columns being part of a bigger table.