Forum Discussion

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 07, 2021

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.

  • markfikry's avatar
    markfikry
    Copper Contributor
    Oct 14, 2021

    Many thanks SergeiBaklan  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(_)

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Oct 24, 2021

      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.

       

      • Szagal's avatar
        Szagal
        Copper Contributor
        Mar 10, 2023

        SergeiBaklan 

        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

         

Resources