Look up value of a column, not containing a specific field in another column

[RichJW]

Hi all,

I hope someone can help with this PBI query.

I need to get a column of data in a table showing the following query…

If Status 'does not equal' "Completed”,

Give me the date of the previous "Reg. Date" of the same "Content"

Test Table

So, in the example above, for Bob it would show 21/06/23

For Martha it would show 04/08/23

I then need to only show those more than a year before [today], so it would only show Bob’s in the example above, however I guess a simple filter on the column would do that.

Any help appreciated.

Thanks all,

Rich

[RichJW]

Hi, I was wrong, this solution doesn't work. It pulls through the last date for every user, whereas I only want it for those with a 'Status' not equal to "Completed". I cannot apply such a filter afterwards, as I've already filtered for "Completed" only in the editor. Any other thoughts, please?

[AlperenA · Accepted solution]

Hi,

In Power Query editor, you can filter column status equal completed than select content and user columns and group by in transform tab. Add date column as aggregation with max operator.