Allocate shift time to Transaction table

[Applicable88]

Hello,

I know many use the concepts of is-statment to allocate certain times to the right shift time. But is it possible to use a prepared 

shift time table to allocate the right shift to the transaction table?

For example my allocation table only consist of three rows:

Now I don't know how to combine this with the Datetime column "StartingTime" of my transactional table, since there is no If smaller or if greater than 6:00am etc...since joins are for exact matches.

How to tell power query when Starting time is between 22:00 to 6am its a nightshift and so fourth with this model?

Thank you very much in advance.

Best. 

[Vera_33 · Accepted solution]

Hi @Applicable88 

I believe there are many ways to do it, you can write nested if then else. Here is one way

sort the reference table, mine is called ShiftTable

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdG7CcMADEXRVYLrQPTkv1cx3n+NpAgYwwFVtxAc6TyHyuc3XZ1XzcdYw/V+xkVxVdwUd8SUYhRbcVScFCWKRJEoEkWilqglaon6FvV9ulKMYity56Q4Ky6Kq+KmuCNGokgUiSJRJIpEkSgSRaJI1BK1RP+/X18=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [StartDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"StartDate", type datetime}}),

    fnSwitch = (input) => let
        values = List.Zip({ShiftTable[From],ShiftTable[Shift]})
    in
        try List.First(List.Select(values, each _{0 }< input)){1} otherwise "Night",

    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each fnSwitch(DateTime.Time([StartDate])))
in
    #"Added Custom"

[Vera_33]

Hi @Syndicate_Admin 

You are asking the same question, I have my answer below you can check. It was generated by Power Query, I simply entered the sample data. The only custom function is that fnSwitch, and you need you need a reference table (mine was called ShiftTable) as another query

[Applicable88]

@Vera_33 , thanks for the quick reply. Can you explain to me the following part. What is that?: 

inary.FromText("bdG7CcMADEXRVYLrQPTkv1cx3n+NpAgYwwFVtxAc6TyHyuc3XZ1XzcdYw/V+xkVxVdwUd8SUYhRbcVScFCWKRJEoEkWilqglaon6FvV9ulKMYity56Q4Ky6Kq+KmuCNGokgUiSJRJIpEkSgSRaJI1BK1RP+/X18=", BinaryEncoding.Base64)

I used a sample data here and cannot put it into my actual data to use. 

Did you wrote the code and can you get there via "clicking" only in PowerQuery? 

Best. 

[Vera_33]

Hi @Applicable88 

It was done by Power Query via Enter Data, simply to put your sample data

if you get a blank query, and paste all the M code to Advanced Editor, you can see the steps, but I did not put the reference table here. So to see the output you need a reference table called ShiftTable as another query, hope it is clear

I wrote this custom function, if your table is not called ShiftTable, and column names are different, please do modify accordingly

    fnSwitch = (input) => let
        values = List.Zip({ShiftTable[From],ShiftTable[Shift]})
    in
        try List.First(List.Select(values, each _{0 }< input)){1} otherwise "Night",

[Applicable88]

@Vera_33 , thank you very much 🙂