locked
Sort,Match & Remove Rows RRS feed

  • Question

  • Hello Community,

    I am trying to sort Receipt No column,once it is short I want to match rows (Example. 500 with 500.1),Indicator should be (Y & N),Count should be (1&-1),Rate should be (positive & negative) and Regiond id should be (positive&negative)once those condition satisfied I want to remove those 2 rows.Please see below data and final result.Does anyone suggest me How can I do in Power Query(language M) or is there any suggestion ?

    Raw Data

    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

    Friday, January 11, 2019 2:05 AM

Answers

  • ABC1919,

    Hmm... It's quite strange. On my computer all works fine (original table and final result you may see below). First of all, please, check your original data - does it same?

    Also, you posted code with additional step #"Changed type". It's redundant and doesn't affect result (since there are no steps refer to it), but for the purity of the experiment, please use the code, which I posted on January, 11.

    The last suggestion - it shouldn't matter, but you may delete this piece of code (step key):

    , "ru-RU"

    • Marked as answer by ABC1919 Monday, January 21, 2019 3:01 PM
    Monday, January 21, 2019 2:53 PM

All replies

  • Hi,

    As far as I understand, you want to remove receipts, which have opposite values (Y vs N, 1 vs -1 etc.). Following code removes receipt, 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.

    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

    Friday, January 11, 2019 11:23 AM
  • Hi Aleksei,

    Thank you so much for your help .You understand the requirements correctly.

    When i have used above script i am getting an error REGION_ID column not available. After that i have removed REGION_ID from the script it worked fine but my result was not right it was same as row data.


     group = Table.Group(transform, {"key"}, {"a", each List.Sum(List.Combine({[Indicator],[Count],[Rate],[#"Region ID"]})), type number}),

    Can you i have your comments on it?

    Saturday, January 12, 2019 7:16 PM
  • Excel 2010 to 2019 Power Query (aka Get & Transform)
    Criteria can be any text or number.
    With added data and pruned headers.
    http://www.mediafire.com/file/bc3cri55d4etgq2/01_13_19.xlsx/file
    http://www.mediafire.com/file/y25bimev67tlheu/01_13_19.pdf/file

    Sunday, January 13, 2019 8:38 PM
  • Hi Herbert,

    thank you so much for your help.

    <herbert>Criteria can be any text or number.

    yes anything text or number is find as long as positive number match with negative number.

    Example :Positive 5000 should match with negative -5000 

    i will try your suggestion and will give you an  update.Thank you so much Again.


    Sunday, January 13, 2019 8:45 PM
  • Hi ABC1919,

    The problem is with spelling the column's name. In your start post - Region ID, and in your comment REGION_ID (i.e. uppercased and with underscore). So if the correct name is REGION_ID you should replace [#"Region ID"] in the code for [REGION_ID].


    Monday, January 14, 2019 10:04 AM
  • Hi Aleksei,

    Please see below my data set and script I am getting Expression Error:The Column ‘Region ID’ of the table wasn’t found.

    Details:

         Region ID

    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

    let

        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Receipt No", type number}, {"Indicator", type text}, {"Count", Int64.Type}, {"Rate", Int64.Type}, {"Region ID", Int64.Type}}),

        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

    Friday, January 18, 2019 3:18 PM
  • Hi Herbert,

    I have tried you solutions and I am not sure my reuslt is not getting right

    Please see my data set below,

    Receipt

    Got

    Count

    Rate

    Region

    Comments

    100

    Y

    64

    68

    CT

    Delete

    200

    Y

    65

    55

    CT

    Keep

    300

    Y

    67

    69

    TX

    keep

    100.1

    N

    -64

    -68

    CT

    Delete

    Final Result does not go with requirements,

    Receipt

    Merged

    100

    Y;64;68;CT

    200

    Y;65;55;CT

    300

    Y;67;69;TX

    It should remove Receipt=100,if Receipt no has .1 then needs find number before .1 which is in my case 100 and it should find same receipt no which is 100 and it should remove.

    Or

    Find positive and negative same number Count (64,-64) then remove the rows.

    Let me know if you have any suggestion.

    Friday, January 18, 2019 3:36 PM
  • Hi ABC1919,

    Now it's clear. I've faced with this problem, when I was writing the code. The code is correct, the problem is with the header in Excel table - it contains unprintable sign. Just type Region ID in the header of original table manually.




    Monday, January 21, 2019 11:03 AM
  • Hi Aleksei,

    Thank you so much for your suggestion,still script is not producing right result,Please see below ,It should remove receipt with 500 & 500.1 rows with indicator "Y"&"N",Count 1&-1 etc.Do you have any other suggestion?

    Final Result Table (Result is not right )

    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

    Monday, January 21, 2019 12:36 PM
  • ABC1919,

    Hmm... It's quite strange. On my computer all works fine (original table and final result you may see below). First of all, please, check your original data - does it same?

    Also, you posted code with additional step #"Changed type". It's redundant and doesn't affect result (since there are no steps refer to it), but for the purity of the experiment, please use the code, which I posted on January, 11.

    The last suggestion - it shouldn't matter, but you may delete this piece of code (step key):

    , "ru-RU"

    • Marked as answer by ABC1919 Monday, January 21, 2019 3:01 PM
    Monday, January 21, 2019 2:53 PM
  • Hi Aleksei,

    Finally it works perfectly fine for me.Thank you so much for your help.

    Monday, January 21, 2019 3:01 PM