Power Query LookUp (Time based)

[vojtechsima]

Hello, guys

I am trying to retrieve a relevant "status" for Date/Time range for each row in Power Query. I have a History table with records, the History Value can be multiply values, I am looking to retrieve "Status" value.
I am using two identical tables, one to retrieve the information, one that I retriefe the information from:

LookupTable (Retrieve From):

StatusTable (Retrieve To):

However, I am getting error, that the "Key" matches to multiply values, however, it should be only one row due to the Formula.
The formula is here:

(let
Key = [Key], NewValueStart = [History New Value Start]
in
Table.SelectRows(LookupTable, each Key=[Key] and [History Field]="status" and NewValueStart >= [History New Value Start] and  NewValueStart <= [History New Value End Fixed]){0}[History Field Value])

 I am using a similar formula in other reports without issues. I am able to resolve this issue in DAX:

with this formula: 

Status = 
var CurrentStatus = maxx(filter('LookupTable','LookupTable'[Key]=TimeInGroups[Key] && 'TimeInGroups'[History New Value Start] >= 'LookupTable'[History New Value Start] && TimeInGroups[History New Value Start] < 'LookupTable'[History New Value End Fixed] && 'LookupTable'[History Field]="status"),'LookupTable'[History New Value])
RETURN
IF(ISBLANK(CurrentStatus),"Not Assigned Yet",CurrentStatus)

However, I wanna do it in Power Query, could you please help me resolve this issue?

[smpa01]

@vojtechsima I can see that you have already written a PQWRY, I am not sure about the ask

[vojtechsima]

Hi, @smpa01 ,
I did not resolve it in Power Query, I am able to resolve it in DAX, but I can't figure it out in Power Query, I sent either a PBIX file or sample data, could you please give me a hint on how to write the code in Power Query, please?

[smpa01 · Accepted solution]

@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 

[smpa01]

@vojtechsima  any chance you can provide sample data and desired output?

[vojtechsima]

Hi, @smpa01 
Here is pasted text as Data source. 

KeyHistory New Value EndHistory New Value StartHistory FieldHistory New ValueTime in New Value (business hrs)Status_Result

SRVN-6236	null	25.10.2021 10:13:57	status	Resolved	6,08	Resolved
SRVN-6236	25.10.2021 10:13:57	19.10.2021 8:06:48	status	In Progress	33,2	In Progress
SRVN-6236	null	19.10.2021 4:44:55	Time To Resolution Status	Breached	39,28	Classification
SRVN-6236	null	13.10.2021 2:02:16	Time To First Response Status	Met	71,28	Classification
SRVN-6236	null	12.10.2021 5:03:52	Responsible Team	Contact Center (VN)	79,28	Classification
SRVN-6236	19.10.2021 8:06:48	12.10.2021 5:03:52	status	Classification	40	Classification
SRVN-6236	12.10.2021 5:03:52	12.10.2021 4:44:44	status	New	0	New
SRVN-6236	12.10.2021 5:03:52	12.10.2021 4:44:43	Responsible Team	Service Desk (VN)	0	Waiting for approval
SRVN-6236	12.10.2021 4:44:44	12.10.2021 4:44:42	status	Waiting for approval	0	Waiting for approval

Thank you

[vojtechsima]

@smpa01 
Hi, sure, here you go.
https://drive.google.com/file/d/1FzGEIuf23cGd8SGlloqjNdNsIt15FSFj/view?usp=sharing
Thank you