Forum Discussion
Count if formula for multiple columns in power query
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
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
groupBackResult is
Didn't catch which exactly mistake did you have. However, good to know if it works finally.
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