Forum Discussion
Power query logic to create a running total based on few conditions
Hi Team,
I have below 2 tables:
Door and Store Status
I want to create a power query logic for below conditions:
1)Door Status is 'Open' and Store Status is 'C' and current devicedid matches with previous deviceid then find the difference (Door Createdon column) between current row and next row. for example,look at the below screenshot, as the above conditions match it finds the difference from there
2)when Door status is closed or Store Status is not closed or current deviceid not matching with previous deviceid then 0 else add current row to the above condition difference.
I have put the above conditions based on excel but in power bi, think we can achieve difference between current row and next row of Createdon and adding them in a single column along with conditions like (Door Status is 'Open' and Store Status is 'C' and current devicedid matches with previous deviceid,when Door status is closed or Store Status is not closed or current deviceid not matching with previous deviceid).
In summary, when Door Status = 'Closed' or Store Status <> 'C' or Current deviceid <> Previous Deviceid then 0, if Door Status = 'Open' && Store Status = 'C' && Current deviceid = Previous Deviceid then sum those minutes from thereon until this conditions break to reset to 0.
PFA file here B&M (4).pbix
Link to Store status excel file Store Status.csv
Please let me know if you need further details and can't access power bi tables.
I can give access to those
Many thanks in advance!
sivarajan21 Hi! I opened the pbix, but i can't access the overflow data source to see door data. However, try with something like:
let
// Step 1: Load your table
Source = YourTable,// Step 2: Sort by DeviceID and CreatedOn
SortedTable = Table.Sort(Source, {{"DeviceID", Order.Ascending}, {"CreatedOn", Order.Ascending}}),// Step 3: Add Index Column for Previous Row Reference
IndexedTable = Table.AddIndexColumn(SortedTable, "Index", 0, 1, Int64.Type),// Step 4: Add Previous Row Columns (DeviceID, CreatedOn, and Store Status)
MergedTable = Table.NestedJoin(IndexedTable, "Index", IndexedTable, "Index", "PreviousRow", JoinKind.LeftOuter),
ExpandedTable = Table.ExpandTableColumn(MergedTable, "PreviousRow", {"DeviceID", "CreatedOn", "StoreStatus", "DoorStatus"}),// Step 5: Calculate Time Difference in Minutes
AddedDuration = Table.AddColumn(ExpandedTable, "TimeDifference", each
if [DoorStatus] = "Open" and [StoreStatus] = "C" and [DeviceID] = [DeviceID.1] then
Duration.TotalMinutes([CreatedOn] - [CreatedOn.1])
else
0,
type number
),// Step 6: Running Sum Until Reset (Grouping by DeviceID)
RunningSum = Table.Group(AddedDuration, {"DeviceID"}, {
{"AllData", each
let
Rows = _,
RunningSumColumn = List.Accumulate(Rows[TimeDifference], {0}, (state, current) =>
if current = 0 then {0} & state else {current + List.First(state)} & List.RemoveFirstN(state, 1)
)
in
Table.FromColumns(Table.ToColumns(Rows) & {List.Reverse(RunningSumColumn)}, Table.ColumnNames(Rows) & {"RunningSum"})
}
}),// Expand the grouped table
ExpandedFinalTable = Table.ExpandTableColumn(RunningSum, "AllData")
in
ExpandedFinalTablelet me know the output and what do you need to fix it.
BBF
2 Replies
sivarajan21 Hi! I opened the pbix, but i can't access the overflow data source to see door data. However, try with something like:
let
// Step 1: Load your table
Source = YourTable,// Step 2: Sort by DeviceID and CreatedOn
SortedTable = Table.Sort(Source, {{"DeviceID", Order.Ascending}, {"CreatedOn", Order.Ascending}}),// Step 3: Add Index Column for Previous Row Reference
IndexedTable = Table.AddIndexColumn(SortedTable, "Index", 0, 1, Int64.Type),// Step 4: Add Previous Row Columns (DeviceID, CreatedOn, and Store Status)
MergedTable = Table.NestedJoin(IndexedTable, "Index", IndexedTable, "Index", "PreviousRow", JoinKind.LeftOuter),
ExpandedTable = Table.ExpandTableColumn(MergedTable, "PreviousRow", {"DeviceID", "CreatedOn", "StoreStatus", "DoorStatus"}),// Step 5: Calculate Time Difference in Minutes
AddedDuration = Table.AddColumn(ExpandedTable, "TimeDifference", each
if [DoorStatus] = "Open" and [StoreStatus] = "C" and [DeviceID] = [DeviceID.1] then
Duration.TotalMinutes([CreatedOn] - [CreatedOn.1])
else
0,
type number
),// Step 6: Running Sum Until Reset (Grouping by DeviceID)
RunningSum = Table.Group(AddedDuration, {"DeviceID"}, {
{"AllData", each
let
Rows = _,
RunningSumColumn = List.Accumulate(Rows[TimeDifference], {0}, (state, current) =>
if current = 0 then {0} & state else {current + List.First(state)} & List.RemoveFirstN(state, 1)
)
in
Table.FromColumns(Table.ToColumns(Rows) & {List.Reverse(RunningSumColumn)}, Table.ColumnNames(Rows) & {"RunningSum"})
}
}),// Expand the grouped table
ExpandedFinalTable = Table.ExpandTableColumn(RunningSum, "AllData")
in
ExpandedFinalTablelet me know the output and what do you need to fix it.
BBF
