Power Query - count across columns if condition met

[jdusek92]

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

[v-piga-msft]

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 · Accepted solution]

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

[jdusek92]

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