Forum Discussion
Advocate IIIPower Query - count across columns if condition met
Hi jdusek92,
You need to do some changes on your data model firstly.
1. In Power Query, select columns 1,2,3,4,5 and click Unpivot columns.
2. Filter rows with value >0
3. Click Group by with Name
In addition, you also could use Dax formula which may meet your desired output better.
After Unpivot the columns 1,2,3,4,5, click Close&Apply, then create a calculated column with the formula below.
count>0 = CALCULATE ( COUNT ( Table1[Attribute] ), FILTER ( 'Table1', 'Table1'[Value] > 0 && 'Table1'[Name] = EARLIER ( Table1[Name] ) ) )Here is the output.
More details, you could refer to this attachment.
Best Regards,
Cherry
Advocate III
Hello, here is a little sample.
I want to simulate the Excel Countifs formula in Power Query.
| Name | 1 | 2 | 3 | 4 | 5 | Count of >0 | Excel formula |
| John | 3 | 2 | 0 | 3 | 2 | =4 | =COUNTIFS(Table1[@[1]:[5]];">0") |
| Alice | 2 | 0 | 0 | 3 | 3 | =3 | =COUNTIFS(Table1[@[1]:[5]];">0") |
| Jacob | 1 | 2 | 3 | 0 | 0 | =3 | =COUNTIFS(Table1[@[1]:[5]];">0") |
Warm regards
Jakub
Resident RockstarHi jdusek92,
You need to do some changes on your data model firstly.
1. In Power Query, select columns 1,2,3,4,5 and click Unpivot columns.
2. Filter rows with value >0
3. Click Group by with Name
In addition, you also could use Dax formula which may meet your desired output better.
After Unpivot the columns 1,2,3,4,5, click Close&Apply, then create a calculated column with the formula below.
count>0 =
CALCULATE (
COUNT ( Table1[Attribute] ),
FILTER (
'Table1',
'Table1'[Value] > 0
&& 'Table1'[Name] = EARLIER ( Table1[Name] )
)
)
Here is the output.
More details, you could refer to this attachment.
Best Regards,
Cherry