So basically i have a sample data similar to this:
| Invoice ID | Type | Weight | Destination |
| 1 | Invoice | 10 | F |
| 2 | Credit note | -10 | F |
| 3 | Invoice | 10 | D |
Where I have a list of invoices with different details and values (this is just an mock up example, can't post the real table). Sometimes due to a mistake while completing an invoice they have to cancell the invoice creating a credit note. See example: first row is the created invoice with destination F, but the correct destination was D, so they first create a credit note that cancells the invoice, and then they create an invoice with the correct destination. Notice how the credit note has a negative numeric value to indicate it's cancelling the other one.
My question is, how can I create a custom column using M in Power query that flags the first two rows where one value is positive and the next one is negative? This is the output i would like to have:
| Invoice ID | Type | Weight | Destination | Delete_Flag |
| 1 | Invoice | 10 | F | Y |
| 2 | Credit note | -10 | F | Y |
| 3 | Invoice | 10 | D | |
Also the reason for cancelling an invoice is not always due to destination being incorrectly completed, it could be some other field. But the weight value will always be negative if it is cancelling a previous invoice. Also the invoice ID is a random generated combination of numbers and letters, for the example I used just one number for simplicity, but it's not as easy as comparing the number of the ID.
My idea was to first create a custom column in M code like this:
Delete_1= if weight<0 then "Y" else ""
Then I wanted to compare the first row that has the same absolute value as the flagged rows where Delete_1="Y", and flag both rows based on that. But i have no idea how to do that 😞
