Count if formula for multiple columns in power query

[awais692]

How to write a formula =countif(A2:M2,"<=15") in power query?

[#2]

@awais692 

If you mean to countif() each row of the table, as variant

- add Index column starting from 0

- add custom column with formula

=List.Accumulate(Record.ToList(#"Added Index"{[Index]}),0, (s,c) => s+Number.From(c<= 15))

[#3]

@Sergei Baklan can you show an example or any screenshot from power query

[#4]

@balajimaikandan 

That could be done in more correct way. Let say we have such table

On the right is result of COUNTIF().

Within Power Query we may do same count by

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    countif = Table.AddColumn(
        Source, "count",
        each List.Count( List.Select( Record.FieldValues(_), each _ <=15 ) ) )
in
    countif

If do step by step, not within one formula for new column

1) Add column Values which contains list of all values for the current row

2) Add another column Select which selects from each list above only values which meet condition

3) Count number of values in each list:

4) Remove intermediate columns

In above I didn't care about errors handling and types assignments which highly recommended to do in real projects. Simplest from above is manually adding type in formula bar when you create custom column, i.e. add manually 4th parameters like

Please check above sample in attached file.

[#5]

Many thanks @Sergei Baklan  for the solution below. 

but I have a problem , in the first step, the table columns not all as numbers some of them are dates and time, so in the second step its give error :

So, how can i select certain range of columns in the first step? Record.FieldValues(_)

[#6]

@markfikry 

You may modify it as

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    countif = Table.AddColumn(
        Source, "count",
        each List.Count( List.Select(
                { [H4], [H5], [H6], [H7], [H8]  } /*Record.FieldValues(_)*/,
                each _ <=15 ) ) )
in
    countif

defining fields directly.

[#7]

Your answer is that I was looking for. Thanks!

[#8]

@RayVega , you are welcome

[#9]

@Sergei Baklan 

I have a similar case.
I have events tables - with times - and openWindow tables - column from to.
I need to check if the event was in openWindow time. Event time>=from AND time<= to.

I know how to do an array formula, but I want to learn PowerQuery.

I import openWindow[from] timen as list and I'm stuck

let
    source = Excel.CurrentWorkbook(){[Name=events]}[Content],
    #Change typ1 = Table.TransformColumnTypes(source,{{time, type time}}),
    #Add column = Table.AddColumn(#Change typ1, fromColumn, each Table.Column(openWindow,from))
in
    #Add column

[#10]

@Szagal 

As variant you may add custom column to Events with OpenWindow table in each record, expand it, add one more custom column to check if the event is in Window and group the result by events taking max value from the latest column. Thus if any TRUE exists it will be returned, otherwise FALSE.

let

    getOpenWindow = Excel.CurrentWorkbook(){[Name="OpenWindow"]}[Content],
    changeOpenWindowType = Table.TransformColumnTypes(
        getOpenWindow,
        {
            {"from", type time},
            {"to", type time}
        }),

    getEvents = Excel.CurrentWorkbook(){[Name="events"]}[Content],
    changeEventsType = Table.TransformColumnTypes(
        getEvents,
        {{"time", type time}}),

    addOpenWindow = Table.AddColumn(
        changeEventsType,
        "Custom",
        each changeOpenWindowType),
    expandWindows = Table.ExpandTableColumn(
        addOpenWindow,
        "Custom",
        {"from", "to"},
        {"from", "to"}),
    checkIfInWindow = Table.AddColumn(
        expandWindows,
        "IsIn",
        each [time] >= [from] and [time] <= [to],
        Logical.Type),
    groupBack = Table.Group(
        checkIfInWindow, {"time"},
        {
            {"inWindowTime", each List.Max([IsIn]), type logical}
        })
in
    groupBack

Result is

[#11]

@Sergei Baklan 

I came to the point of "checkIfInWindow" by clicking :).
Then row multiplied - I considered it a mistake.

So after grouping by max(IsIn) stay 1  (true), it seems to work.

Thanks

[#12]

@Szagal 

Didn't catch which exactly mistake did you have. However, good to know if it works finally.