Power Query Editor - Remove Rows - Conditional

[G_Whit-UK]

Hi,

My data set has a number of transaction references and transaction dates (amoungst other fields).  On the dates when the transaction closes, I get two lines for the same date, due to having different transaction status codes ("Settled" vs "Closed").   When a transaction is still open (in the defined date range), the status code will be relfetced as "Settled".  I'd like to filter the data so that the duplicated dates only reflect the "Closed" line, but still retain the data for the transactions which are still in "Settled" status.  I've attempted the "Group By" tool, but I cannot find a conditional statement to make it select the "Closed" How do I go about getting the required output?

Thanks.

Here is an example:  I'd like the final version of the data to exclude the two highlighted lines while retaining all the others.

Data Example

[MFelix · Accepted solution]

Hi @G_Whit-UK ,

In this case you can use the group by option but for the aggregation select the minimum value since you want the closed.

Check the code below with an example:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hc89CsAgDAXguzgLJrGJ2dtuQgfdxPtfo7Fbf6Tje3y8kNZcBFaNlJx3uASkQIBsoey15n1z3Q8joqRkNenUJGSAaDVIABxGLKz5KD/kPqOMItepNJv5Jo8ZjSrjKwNAL9NP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Num = _t, LOAN_STETTLEMENT_DATE = _t, LOAN_STATUS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Num", Int64.Type}, {"LOAN_STETTLEMENT_DATE", type date}, {"LOAN_STATUS", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Num", "LOAN_STETTLEMENT_DATE"}, {{"LOAN_STATUS", each List.Min([LOAN_STATUS]), type text}})
in
    #"Grouped Rows"

Regards,

MFelix

[G_Whit-UK]

Thanks @MFelix 

Rather than work with raw code, I was able to get it to work using the advanced feature within the "Group By" tool - which I found to be a bit easier.

Test Ouput

[MFelix]

Hi @G_Whit-UK ,

The raw code I have send out to you was for you to have the example,  by copy pasting to advance editor you would be abble to see the step by step that I have made. I achieve the result with the same tool as you and not by direct coding.

Regards,

MFelix