Return value if date falls in between two dates in Power Query?

Question

Employees have a certain time frame to complete an action and get a result. The time frame may crossover from one day to the next. They may have to complete the action more than once per day. I need to match the action datetime to the time frame and keep all the rows in the source table.

Source Table:

Empl ID | Start DateTime | End DateTime

123 | 9/30 10:00 PM | 10/1 1:00 AM

456 | 10/1 7:00 AM | 10/1 10:00 AM

456 | 10/1 5:00 PM | 10/1 10:00 PM

Lookup Table:

Empl ID | Action DateTime | Result

123 | 9/30 11:00 PM | A

456 | 10/1 9:30 AM | B

456 | 10/1 11:15 PM | B

End Result:

Empl ID | Start DateTime | End DateTime | Action DateTime | Result

123 | 9/30 10:00 PM | 10/1 1:00 AM | 9/30 11:00 PM | A

456 | 10/1 7:00 AM | 10/1 10:00 AM | 10/1 9:30 AM | B

456 | 10/1 5:00 PM | 10/1 10:00 PM | null | null

I have tried the following code but it is abysmally slow:

let
    Source = #"SourceTable",
    LBuffered = Table.Buffer(LookupTable),
    #"Added Custom" = Table.AddColumn(Source, "LookupTable", (#"SourceTable") => Table.SelectRows(LBuffered, each #"SourceTable"[Empl ID] = [Empl ID] and #"SourceTable"[Compliance StartDateTime] <= [Action DateTime] and #"SourceTable"[End DateTime] >= [Action DateTime])),
    #"Expanded LookupTable" = Table.ExpandTableColumn(#"Added Custom", "LookupTable", {"Action DateTime",  "Result"}, {"Action DateTime", "Result"})

(other code)

Is there another way I'm not thinking of to speed up this process?

Hard to tell without the complete picture (for example how many records there are, where are the tables stored, what transformations are happening at the source and lookup tables) — Ricardo Diaz
@ricardiodiaz the "source table" and "lookup table" are each in their own query that i thought to combine in a "end result" query. There could be thousands of records per day. I'm testing this on one week of 27,000 source table records. I create the start and end times on the source table through a simple custom formula (shift time + or - 2 hours). — Bianca Larenas
You most likely have other transformations. On my end, applying the query that you have to 45k records takes less than 1 sec. It's probably better to review each step and see where is the bottleneck. — Ricardo Diaz
@RicardoDiaz they come from excel and csv. I'm combining CSV files for each day into one query if that makes a difference. All my power query knowledge is self-taught so sometimes it's hard to know what I don't know. — Bianca Larenas

Chris Chris · Accepted Answer · 2020-10-15T12:54:53

I think join could be faster. In my example the DateTime-Columns are TEXT, so I converted them to DateTime. If your source has DateTime-Columns you haven't to do this step and save time.

I create a list for all relevant dates per 'Empl ID' for every 15 Minutes. (In your Lookup-Table examples there are time in 15 Min. step, you can change the M-Code to every Minute if necessary )

/*Source Table*/

let
    Quelle = Excel.CurrentWorkbook(){[Name="Source_Table"]}[Content],
    Custom_StartDateTimeNew = Table.AddColumn(Quelle, "Start DateTimeNew", each 
        #datetime(Date.Year(DateTime.LocalNow()), 
                    Number.From(Text.BeforeDelimiter([#"Start DateTime "], "/")), 
                    Number.From(Text.BetweenDelimiters([#"Start DateTime "], "/", " ")), 
                    Time.Hour(Time.From(Text.AfterDelimiter([#"Start DateTime "], " "))), 
                    Time.Minute(Time.From(Text.AfterDelimiter([#"Start DateTime "], " "))), 0)
        , type datetime),
    
    Custom_EndDateTimeNew = Table.AddColumn(Custom_StartDateTimeNew, "End DateTimeNew", each 
        #datetime(Date.Year(DateTime.LocalNow()),
                    Number.From(Text.BeforeDelimiter([#"End DateTime"], "/")), 
                    Number.From(Text.BetweenDelimiters([#"End DateTime"], "/", " ")), 
                    Time.Hour(Time.From(Text.AfterDelimiter([#"End DateTime"], " "))), 
                    Time.Minute(Time.From(Text.AfterDelimiter([#"End DateTime"], " "))), 0)
        , type datetime),
    
    Custom_ListDateTime = Table.AddColumn(Custom_EndDateTimeNew, "List DateTime", each 
        List.DateTimes([Start DateTimeNew], 
                        (Number.From([End DateTimeNew] - [Start DateTimeNew]))*96+1, 
                        #duration(0,0,15,0))),
    Expand_ListDateTime = Table.ExpandListColumn(Custom_ListDateTime, "List DateTime")
in
    Expand_ListDateTime

Lookup:

/*Lookup Table*/

let
    Quelle = Excel.CurrentWorkbook(){[Name="Lookup_Table"]}[Content],
    #"Geänderter Typ" = Table.TransformColumnTypes(Quelle,{{"Empl ID ", Int64.Type}, {"Action DateTime", type text}, {"Result", type text}}),
    Custom_ActionDateTimeNew = Table.AddColumn(#"Geänderter Typ", "Action DateTimeNew", each 
        #datetime(Date.Year(DateTime.LocalNow()),
                    Number.From(Text.BeforeDelimiter([#"Action DateTime"], "/")), 
                    Number.From(Text.BetweenDelimiters([#"Action DateTime"], "/", " ")), 
                    Time.Hour(Time.From(Text.AfterDelimiter([#"Action DateTime"], " "))), 
                    Time.Minute(Time.From(Text.AfterDelimiter([#"Action DateTime"], " "))), 0))
in
    Custom_ActionDateTimeNew

Result

/*Result Table*/
let
    Quelle = Table.NestedJoin(Source_Table, {"Empl ID ", "List DateTime"}, Lookup_Table, {"Empl ID ", "Action DateTimeNew"}, "Lookup_Table", JoinKind.LeftOuter),
    Expand_Lookup_Table = Table.ExpandTableColumn(Quelle, "Lookup_Table", {"Action DateTime", "Result"}, {"Action DateTime", "Result"}),
    RemoveOtherColumns = Table.SelectColumns(Expand_Lookup_Table,{"Empl ID ", "Start DateTime ", "End DateTime", "Action DateTime", "Result"}),
    #"Gruppierte Zeilen" = Table.Group(RemoveOtherColumns, {"Empl ID ", "Start DateTime ", "End DateTime"}, {{"Action DateTime", each List.Max([Action DateTime]), type nullable text}, {"Result", each List.Max([Result]), type nullable text}})
in
    #"Gruppierte Zeilen"

Return value if date falls in between two dates in Power Query?

1 Answers