Forum Discussion

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

     

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Mar 11, 2023

      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

       

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

        SergeiBaklan 

        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

  • RayVega's avatar
    RayVega
    Copper Contributor
    Oct 26, 2021
    Your answer is that I was looking for. Thanks!

Resources