Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |