CountIF by rows in multiple columns on powerquery

[Pivotable78]

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🙏

[Pivotable78]

Hi,

It worked thanks for your help.

[v-yanjiang-msft · Accepted solution]

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"
) + 0

Get the result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pivotable78]

Hi v-kalyj-msft ,

Thank you very much.

I will check that 😉