Remove rows based on complex condition and other rows

Question

Hi, I have the table like below in Power BI, I want to remove some rows based on condition. if the same row is added and removed just for a change of case those two records need to be removed.&nbsp;Original Table&nbsp;ID&nbsp; &nbsp; File&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Value&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IsValueAddition------------------------------------------------------------------------1&nbsp; &nbsp; &nbsp;1.txt&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6/8/2020 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ABC &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; true2&nbsp; &nbsp; &nbsp;2.txt&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;8/9/2020 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;PqR&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;true3&nbsp; &nbsp; &nbsp;3.txt &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 8/10/2020 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;xYz&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; true4&nbsp; &nbsp; &nbsp;2.txt&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/11/2020 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;PqR &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; false5&nbsp; &nbsp; &nbsp;2.txt&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/11/2020 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;PQR&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; true6&nbsp; &nbsp; &nbsp;1.txt&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;9/18/2020&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ABC&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; false7&nbsp; &nbsp; &nbsp;4.txt&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10/2/2020 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;dEF&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;true &nbsp;&nbsp;Output Table after removals&nbsp;ID&nbsp; &nbsp; File&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Value&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; IsValueAddition------------------------------------------------------------------------2&nbsp; &nbsp; &nbsp;2.txt&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;8/9/2020 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;PqR&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;true3&nbsp; &nbsp; &nbsp;3.txt &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 8/10/2020 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;xYz&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; true7&nbsp; &nbsp; &nbsp;4.txt&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;10/2/2020 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;dEF&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; true &nbsp;&nbsp;&nbsp;Notes:&nbsp;1) The Row's 1 and 6&nbsp; removed because the same content was added and the removed the content (Technically nothing written to file)&nbsp;2) The Row's 4 and 5 removed because the two rows technically added just to change the case of the value.&nbsp;&nbsp;Appreciate any help to solve this issue.&nbsp;

lbendlin · Accepted Answer

1. for each combination of file and Uppercase of Value, add 1 for true and subtract 1 for false - into a new aggregated table
2. if the result is 1, select the first row for that combination&nbsp;
3. Discard all other rows.
&nbsp;
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nRRAAK3zJxUBRTgkliCJgIEYYk5pciinsVgEceUlMySzPw8pVidaCVDsIyhXklFCYpeM30LfSMDIwMUQUcnZxR+SVFpKtgUIzDXCMMUC31LTFMCCoOwm2IM5hpjMcXQAMOYisgq7KaY4HCLpb6hIYYp6G5JS8wphhhjSpIxgTi8ZAbmYgYv0BTM8EUPXoRjzMF8EwxjQOGCYUyKqxsWx8QCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Split Column by Positions" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByPositions({0, 6, 24, 45, 61}), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Split Column by Positions", [PromoteAllScalars=true]),
    #"Uppercased Text" = Table.TransformColumns(#"Promoted Headers",{{"Value           ", Text.Upper, type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Uppercased Text","true","1",Replacer.ReplaceText,{"IsValueAddition"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","false","-1",Replacer.ReplaceText,{"IsValueAddition"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value1",{{"IsValueAddition", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"File              ", "Value           "}, {{"Count", each List.Sum([IsValueAddition]), type nullable number}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Count] = 1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"File              "}),
    #"Merged Queries" = Table.NestedJoin(#"Promoted Headers", {"File              "}, #"Removed Other Columns", {"File              "}, "Removed Other Columns", JoinKind.Inner),
    #"Removed Other Columns1" = Table.SelectColumns(#"Merged Queries",{"ID    ", "File              ", "Date                 ", "Value           ", "IsValueAddition"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns1", {"File              "})
in
    #"Removed Duplicates"
&nbsp;
Something like this, just more elegant 🙂&nbsp;&nbsp;

Forum Discussion

Remove rows based on complex condition and other rows

1 Reply

Related content

Conditional column with complex multiple conditions

Complex Conditional filtering

Complex Conditional Column

Complex duplication removal process

Remove duplicates - quite complex ask