Forum Discussion
Allocate shift time to Transaction table
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.
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"
5 Replies
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"Anonymous , 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.
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",
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
