Forum Discussion
Power 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
Hello,
I have a query that has employees in rows and days (1-31) in columns = 31 additional columns - pivoted. These columns contain numbers from 0 to 3. I want to add new column that will return the count of days columns (in row) that have value >0.
As a work around I replaces 0 values with null and than added a calculated column with count values option.
I guess that there will be another way how to do that by altering this generated SUM formula:
=List.Sum({[1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15], [16], [17], [18], [19], [20], [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31]})Can anyone help?
I want to keep the original values (1;2;3) so replacing them with 1 and using SUM is not an option
also I don't want to use "brute force" by nesting multiple IFS >0.
Warm regards
Jakub DuĊĦek