Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jdusek92
Helper IV
Helper IV

Power 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

 

1 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.

 

Capture.PNG

More details, you could refer to this attachment. 

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-piga-msft
Resident Rockstar
Resident 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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 Hello, here is a little sample.

I want to simulate the Excel Countifs formula in Power Query.

 

 Name2345Count of >0Excel formula
John32032=4=COUNTIFS(Table1[@[1]:[5]];">0")
Alice20033=3=COUNTIFS(Table1[@[1]:[5]];">0")
Jacob12300=3=COUNTIFS(Table1[@[1]:[5]];">0")

 

Warm regards

Jakub

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.

 

Capture.PNG

More details, you could refer to this attachment. 

 

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jdusek92
Helper IV
Helper IV

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.