Power Query LookUp (Time based)

vojtechsima  okay. The direct translation of  MAXX in PQ would be following. PBIX is attached

let
    a1 = Table.RenameColumns(let
        v1 = let
            v2 = Table.PrefixColumns(TimeInGroups, "a"),
            v3 = Table.PrefixColumns(LookupTable, "b"),
            v4 = Table.Join(v2, {"a.Key"}, v3, {"b.Key"}, JoinKind.LeftOuter, null)
        in
            v4,
        v6 = Table.SelectRows(v1, (v5) => Value.NullableEquals(v5[b.History_Field], "status")),
        v7 = Table.RenameColumns(v6, {{"b.History_New_Value_Start", "History_New_Value_StartB"}, {"b.History_New_Value", "History_New_ValueB"}}),
        v8 = Table.SelectColumns(v7, {"a.Index", "a.Key", "a.History_New_Value_End", "a.History_New_Value_Start", "a.History_Field", "a.History_New_Value", "a.Time_in_New_Value_businessHrs", "History_New_Value_StartB", "History_New_ValueB", "b.History_New_Value_End_Fixed"})
    in
        v8, {{"a.Index", "a1.Index"}, {"a.Key", "a1.Key"}, {"a.History_New_Value_End", "a1.History_New_Value_End"}, {"a.History_New_Value_Start", "a1.History_New_Value_Start"}, {"a.History_Field", "a1.History_Field"}, {"a.History_New_Value", "a1.History_New_Value"}, {"a.Time_in_New_Value_businessHrs", "a1.Time_in_New_Value_businessHrs"}, {"History_New_Value_StartB", "a1.History_New_Value_StartB"}, {"History_New_ValueB", "a1.History_New_ValueB"}, {"b.History_New_Value_End_Fixed", "a1.History_New_Value_End_Fixed"}})
in
    Table.RenameColumns(let
        v9 = a1,
        v11 = Table.SelectRows(v9, (v10) => v10[a1.History_New_Value_StartB] <= v10[a1.History_New_Value_Start] and v10[a1.History_New_Value_End_Fixed] > v10[a1.History_New_Value_Start]),
        v12 = Table.Group(v11, {"a1.Key", "a1.History_New_Value_End", "a1.History_New_Value_Start", "a1.History_Field", "a1.History_New_Value", "a1.Time_in_New_Value_businessHrs"}, {{"Status", (v13) => List.Max(v13[a1.History_New_ValueB])}}),
        v14 = Table.SelectColumns(v12, {"a1.Key", "a1.History_New_Value_End", "a1.History_New_Value_Start", "a1.History_Field", "a1.History_New_Value", "a1.Time_in_New_Value_businessHrs", "Status"})
    in
        v14, {{"a1.Key", "Key"}, {"a1.History_New_Value_End", "History_New_Value_End"}, {"a1.History_New_Value_Start", "History_New_Value_Start"}, {"a1.History_Field", "History_Field"}, {"a1.History_New_Value", "History_New_Value"}, {"a1.Time_in_New_Value_businessHrs", "Time_in_New_Value_businessHrs"}, {"Status", "Status"}})

 The above is written to mimic the following SQL

SELECT a.[key],
       a.history_new_value_end,
       a.history_new_value_start,
       a.history_field,
       a.history_new_value,
       a.time_in_new_value_business_hrs,
       Max(b.history_new_value) AS [Status]
FROM   [dbo].[timeingroups] a
       LEFT OUTER JOIN [dbo].[lookuptable] b
                    ON a.[key] = b.[key]
                       AND b.history_field = 'status'
                       AND b.history_new_value_start <= a.[history_new_value_start]
                       AND b.[history_new_value_end_fixed] > a.[history_new_value_start]
GROUP  BY a.[key],
          a.history_new_value_end,
          a.history_new_value_start,
          a.history_field,
          a.history_new_value,
          a.time_in_new_value_business_hrs