Forum Discussion
Allocate shift time to Transaction table
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"
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",Anonymous , thank you very much 🙂
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
