Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to Solution.
@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
ExpandedFinalTable
let me know the output and what do you need to fix it.
BBF
@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
ExpandedFinalTable
let me know the output and what do you need to fix it.
BBF
Check out the July 2025 Power BI update to learn about new features.