Forum Discussion
PowerQuery Countifs across multiple columns
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 🙂
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
3 Replies
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
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.
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
