Forum Discussion
Custom Column (Similar to countif in excel) 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 🙂
Hi jlys89 ,
According to your requirements, I created the corresponding data model according to the data you provided. I did the following test: first, create two columns of column1 and column2 according to the provided conditions, judge whether the rows that meet the requirements are set to 1, and the created Measure is used to count the number of different categories. Finally, the correct result is obtained.
Best Regards,
HenryIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- amitchandak
Super User
jlys89 , Try two new columns like
Category 1 = (if [Feature 1] <> null then 1 else 0 + if [Feature 2] <> null then 1 else 0 + if [Feature 3] <> null then 1 else 0 )/3
Category 2 = (if [Feature 2] <> null then 1 else 0 + if [Feature 4] <> null then 1 else 0 + if [Feature 6] <> null then 1 else 0 )/3
v-henryk-mstfCommunity Support
Hi jlys89 ,
According to your requirements, I created the corresponding data model according to the data you provided. I did the following test: first, create two columns of column1 and column2 according to the provided conditions, judge whether the rows that meet the requirements are set to 1, and the created Measure is used to count the number of different categories. Finally, the correct result is obtained.
Best Regards,
HenryIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
