Forum Discussion
CountIF by rows in multiple columns on powerquery
Hi,
I'm struglling to find solution to do this in power query and didn't find here :
I get a report from salesforces that shows activity by sales reps by day that i load on power query.
it looks like this (without red columns) :
| Rep | Monday | Tuesday | Wednesday | Thursday | Friday | Date | On field | out of field |
| Name A | On field | On field | On field | out of field | out of field | November | 3 | 2 |
| Name B | On field | out of field | out of field | out of field | out of field | November | 1 | 4 |
| Name A | On field | On field | On field | On field | On field | December | 5 | 0 |
What I want is to sum for each month the number of values "On field" and "out of field" like the red columns then fill another file with xlookup.
Thank you for your help🙏
Hi Pivotable78 ,
According to your description, here's my solution.
1. In Power Query, select the columns "Monday, Tuesday, Wednesday, Thursday, Friday" (Ctrl+select).
2. Click "Unpivot Columns" tab under the Transform ribbon.
Get this table.
3.Create two measures.
Count on field = CALCULATE ( COUNT ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Rep], 'Table'[Month] ), 'Table'[Value] = "On field" )Count out field = CALCULATE ( COUNT ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Rep], 'Table'[Month] ), 'Table'[Value] = "Out of field" ) + 0Get the result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
v-yanjiang-msftCommunity Support
Hi Pivotable78 ,
According to your description, here's my solution.
1. In Power Query, select the columns "Monday, Tuesday, Wednesday, Thursday, Friday" (Ctrl+select).
2. Click "Unpivot Columns" tab under the Transform ribbon.
Get this table.
3.Create two measures.
Count on field = CALCULATE ( COUNT ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Rep], 'Table'[Month] ), 'Table'[Value] = "On field" )Count out field = CALCULATE ( COUNT ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Rep], 'Table'[Month] ), 'Table'[Value] = "Out of field" ) + 0Get the result.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyjIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi
I will check that 😉
Hi,
It worked thanks for your help.
