A bit stumped here.
Trying to enhance a dataset with a column that uses some logic.
The data is coming from general ledger entries with the the two (or more) accounts an document posts to. What I need to do is determine which lines of a document contain the entirety of an action. I know when the total of the rows = 0, that's a complete action.
The data is in order, so starting from row 1 we see that adding row 2 sums to 0 thus one entire action. Similarly, 3 & 4, one entire action. 5, 6, and 7 one action. And so one.
Expected results (label/count actions or flag row):
I think there are two ways to accomplish this, assuming your data structure looks like what you've provided. To me, this feels like this flag should be at the grain of the data, so it could be included as a calculated column in your data source. So, what I've done is sorted the data by Doc, and then Line, and then just created an Index from within the Power Query Editor, with the "starting at 1" option:
From there, I created a calculated column with the following logic:
Unfortunately, I messed up my sample data. The real data doesn't always have the negative values last, hence the need to sum until we get a zero total.
Updated sample data:
Whichever result is easiest to produce.
I could use any of these result columns:
Got it. I think this works, but I created this as a measure now instead of a calculated column like the previous approach. I set this up to represent your "Flag(end)." This could be easily modified for the other scenario if that's desired, though. Here's the new calc:
Not sure what your data architecture/data structure looks like, but if I were you, I would try to put a calculation like this on the DB side. It's at the lowest grain of data and could just be incorporated into your driving table.