How to compare a time from transaction table with shift table that has different shifts in M.

[Nicki]

I have a table(A) that has different time for each event_id. And also I have a shift table(B) that has different shift times.

I want to check the time  in table (A) with  [Start time] and [EndTime] in shfit table[B} and determine the shiftID.

In this case I can have a slicer that connect to table B and can create a relationship between table A and B with ShiftID.

Table A:                                                                      Table B(shift table)

1      6:30  AM                                                            6:00 AM - 2:00 PM  morning 

2     5:30  PM                                                             2:00 PM -  11 PM Evening 

3    4:00  AM                                                             11:00  PM-  6:00  AM  Night

How can determing the 6:30 is  shift morning?  

Thank you in advance. 

[v-ljerr-msft]

Hi @Nicki,

Based on my test, you should be able to use the formula below to create a new calculate column in table(A) to get the corresponding shiftID in your scenario. 

Column =
IF (
    Table1[Time] > TIMEVALUE ( "6:00 AM" )
        && Table1[Time] <= TIMEVALUE ( "2:00 PM" ),
    "Morning",
    IF (
        Table1[Time] > TIMEVALUE ( "2:00 PM" )
            && Table1[Time] <= TIMEVALUE ( "11:00 PM" ),
        "Evening",
        IF (
            Table1[Time] > TIMEVALUE ( "11:00 PM" )
                || Table1[Time] <= TIMEVALUE ( "6:00 AM" ),
            "Night"
        )
    )
)

Regards

[Nicki]

Thank you for responds.

It works fine in DAX. If I want to apply this soultion in M how it can be.

If we create a calculate column in DAX we cannot create a relationship between two table to filter data based on slicer selection.

[MarcelBeug · Accepted solution]

In Power Query (M) it can be done with an approximate lookup. In general this works fastest if the lookup table is converted to a buffered list.

This is Table B:

Notice the buffered list, consist of 3 items, each with 3 nested items {0) = Time From, {1} = Time To and {2} = Shift.

The last item is selected with Time From less than or equal to the time in Table A; if no time is less than or equal to the time in Table A (< 6:00 AM), then the second parameter of List.Last, default value List.Last(BufferdListB), kicks in.

let
    Source = #"Table A",
    BufferedListB = List.Buffer(Table.ToRows(#"Table B")),
    AddedShift = Table.AddColumn(Source, "Shift", (earlier) => List.Last(List.Select(BufferedListB, each _{0} <= earlier[Time]),List.Last(BufferedListB)){2}, type text)
in
    AddedShift