Forum Discussion
Solved
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 d...
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
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
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