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.

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

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

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

3 Replies

v-ljerr-msft
Microsoft Employee
8 years ago
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. :smileyhappy:

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
  Helper III
  8 years ago
  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
    Community Champion
    8 years ago
    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

Forum Discussion

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