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
Rochejf
3 years agoFrequent Visitor
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