Forum Discussion
Power Query Editor - Remove Rows - Conditional
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
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
3 Replies
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
