Results 1 to 4 of 4

Thread: Remove Rows based on condition
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2018
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular
    Join Date
    Nov 2016
    Posts
    87
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    New Member
    Join Date
    Nov 2018
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    New Member
    Join Date
    Nov 2018
    Posts
    19
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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