Thanks:  0
Likes:  0

# Thread: Remove Rows based on condition

1. ## Remove Rows based on condition

Hello,

I want to remove receipts no which have opposite values (Y vs N,1 vs -1 etc.).if each receipt's row has opposite row. I.e. if also will be row 500.2 (with any content) - receipt 500 will not be removed.

 Receipt No Indicator Count Rate Region ID 500 N 1 100 11 600 N 1 200 12 601 N 1 300 13 500.1 Y -1 -100 -11 603 N 1 400 14 Final Result Receipt No Indicator Count Rate Region ID Comments 500 N 1 100 11 Remove 500.1 Y -1 -100 -11 Remove 600 N 1 200 12 Keep 601.1 N 1 300 13 Keep 603.2 N 1 400 14 Keep

2. ## Re: Remove Rows based on condition

I think you need to clarify your question please. I know it's not easy posting in a foreign language but your initial sentences makes no sense, try breaking them into smaller pieces perhaps? I don't understand how the Final Result can be generated when some of the data does not exist in the original source. What are the rules to create 601.1 and 603.2 for example. Can you post the code you have tried so far?
Peter

3. ## Re: Remove Rows based on condition

Hi Peter,
First formula should check Invoice no with .1 Example 100.1 and after that in the same row it should also find related rows Example 100.if those condition match then remove those 2 rows.
Example
Receipt No with .1 Example 100.1 then find 100.

4. ## Re: Remove Rows based on condition

What are the rules to create 601.1 and 603.2 for example. Can you post the code you have tried so far?
Rule is if receipt no is without .1 or any unique no i want to keep it .
Solutons 1:
let
Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
key = Table.AddColumn(Source, "key", each Text.BeforeDelimiter(Text.From([Receipt No], "ru-RU"), "."), type text),
transform = Table.TransformColumns(key, {"Indicator", each Record.FieldOrDefault([N = -1, Y = 1], _, _)}),
group = Table.Group(transform, {"key"}, {"a", each List.Sum(List.Combine({[Indicator],[Count],[Rate],[#"Region ID"]})), type number}),
list = Table.SelectRows(group, each [a] = 0)[key],
filter = Table.SelectRows(key, each not List.Contains(list, [key])),
final = Table.RemoveColumns(filter,{"key"})
in
final
Above query gets an error “Region id”column not found ,I have removed region_id column and scripts works fine but did not give me correct result.
Solutions:2
Below formula works for me but I have a duplicate data in my dataset and I am getting “A table of multiple values was supplied where a single value was expceted error” I am not sure how to fix?
Check =
IF (
LOOKUPVALUE (
'Raw Data'[No],
'Raw Data'[Count], 'Raw Data'[Count] * -1,
'Raw Data'[Avg], 'Raw Data'[Avg] * -1,
'Raw Data'[Curr], 'Raw Data'[Curr] * -1
)
<> BLANK (),
"A",
"B"
)