cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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

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. 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

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

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:

 

Table B.png

 

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
Specializing in Power Query Formula Language (M)

View solution in original post

3 REPLIES 3
Highlighted
Microsoft
Microsoft

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

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. Smiley Happy

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"
        )
    )
)

c1.PNG

 

Regards

Highlighted
Helper III
Helper III

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

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.

 

 

 

Highlighted
Community Champion
Community Champion

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

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:

 

Table B.png

 

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
Specializing in Power Query Formula Language (M)

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (745)