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?
Good luck solving your problem
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.
please let me know if you need more information.

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 .
Please see below code ,
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"
)

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•