Forum Discussion
Rochejf
3 years agoFrequent Visitor
Sum of Time differences by two condition
Hi I really need some help here. Referring to the image attached, I want to find the time difference in a logic of column TRANSACTION_STATUS(7-4). Probably this can be done in way with t...
- 3 years ago
Thanks!
The code works out perfectly.However, after i swapped it to powerbi loaded database, it shows token identifier expected. How should i amend the code?
let Source = BATCH_RUN_STEP_LABOR_TRANSACTION, let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, TRANSACATION_DATE_TIME = _t, TRANSACTION_STATUS = _t, BATCH_RUN_STEP_LABOR_ID = _t]), #"Changed Type" = Table.TransformColumnTypes(Source, { {"ID", Int64.Type}, {"TRANSACATION_DATE_TIME", type datetime}, {"TRANSACTION_STATUS", Int64.Type}, {"BATCH_RUN_STEP_LABOR_ID", Int64.Type} }, "es"), #"Added Custom" = Table.AddColumn(#"Changed Type", "Time Spent", each if [TRANSACTION_STATUS] = 7 then [TRANSACATION_DATE_TIME] - Table.Sort( Table.SelectRows( #"Changed Type", (k) => k[TRANSACTION_STATUS] = 4 and k[TRANSACATION_DATE_TIME] < [TRANSACATION_DATE_TIME] ), {{"TRANSACATION_DATE_TIME", Order.Descending}} ){0}[TRANSACATION_DATE_TIME] else null, type duration ), #"Grouped Rows" = "" in #"Grouped Rows"Hope you can assist me again on this manner