Forum Discussion
Advocate IIIPower Query - count across columns if condition met
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
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
4 Replies
jdusek92Hello,
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
v-piga-msftResident Rockstar
Hi jdusek92,
Based on your information, I'm still a little confused about your scenario.
If it is convenient, could you share some data sample and your desired output so that I can understand your scenario better and get the solution.
Best Regards,
Cherry
- jdusek92
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
- v-piga-msft
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