Filter First Date by First Negative Value

[bernate]

Hello, I am trying to filter a table in Power Query to show the first month an item will have a negative balance. I want to identify the columns and mark them with a 1, the remove the rows in the Desired column that equal 0.

I did this with DAX by using the formula CALCULATE(FIRSTDATE('Buy Comp Balances - Open POs On'[Date]),'Buy Comp Balances - Open POs On'[Balance]<0).

I want to do this specifically in Power Query to decrease the size of my table and make the Item column have distinct values, so that the table would look like below:

[Anonymous · Accepted solution]

Hi @bernate 

Please try the following steps:

First copy a table and operate on the copied table

1. Filter out all negative values

2. Filter by item as a group to find the minimum date for each item

3. Merge the two tables (Select two columns to match)

4. Expand the table

5. Click on the Close & Apply

6. Output

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.