Remove Rows based on condition

[tejapowerbi]

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.

[TABLE="width: 463"]
<tbody>[TR]
[TD]Receipt No[/TD]
[TD]Indicator[/TD]
[TD]Count[/TD]
[TD]Rate[/TD]
[TD]Region ID[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]500[/TD]
[TD]N[/TD]
[TD]1[/TD]
[TD]100[/TD]
[TD]11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]600[/TD]
[TD]N[/TD]
[TD]1[/TD]
[TD]200[/TD]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]601[/TD]
[TD]N[/TD]
[TD]1[/TD]
[TD]300[/TD]
[TD]13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]500.1[/TD]
[TD]Y[/TD]
[TD]-1[/TD]
[TD]-100[/TD]
[TD]-11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]603[/TD]
[TD]N[/TD]
[TD]1[/TD]
[TD]400[/TD]
[TD]14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]Final Result[/TD]
[/TR]
[TR]
[TD]Receipt No[/TD]
[TD]Indicator[/TD]
[TD]Count[/TD]
[TD]Rate[/TD]
[TD]Region ID[/TD]
[TD]Comments[/TD]
[/TR]
[TR]
[TD]500[/TD]
[TD]N[/TD]
[TD]1[/TD]
[TD]100[/TD]
[TD]11[/TD]
[TD]Remove[/TD]
[/TR]
[TR]
[TD]500.1[/TD]
[TD]Y[/TD]
[TD]-1[/TD]
[TD]-100[/TD]
[TD]-11[/TD]
[TD]Remove[/TD]
[/TR]
[TR]
[TD]600[/TD]
[TD]N[/TD]
[TD]1[/TD]
[TD]200[/TD]
[TD]12[/TD]
[TD]Keep[/TD]
[/TR]
[TR]
[TD]601.1[/TD]
[TD]N[/TD]
[TD]1[/TD]
[TD]300[/TD]
[TD]13[/TD]
[TD]Keep[/TD]
[/TR]
[TR]
[TD]603.2[/TD]
[TD]N[/TD]
[TD]1[/TD]
[TD]400[/TD]
[TD]14[/TD]
[TD]Keep[/TD]
[/TR]
</tbody>[/TABLE]

 

[peter789]

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

 

[tejapowerbi]

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.

 

[tejapowerbi]

<Peter>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"
)