Forum Discussion

shalinik's avatar
shalinik
Frequent Visitor
3 years ago

Delete rows based on conditions

Hi community, 

I want to delete rows based on few conditions. If fileds doc_numer , date , intance have same values and In field pay_doc_no any one or more row has value start with 2 or 1 then delete all rows from data as they have same doc_numer , date , intance and all condtion should true if not then we have to include rows in report.
I need to perform this transforamtion in power query . I attached my sample data and highlighed rows which needs to delete .please refer my comments filed.

 

Need Help

3 Replies

  • adudani's avatar
    adudani
    Memorable Member
    3 years ago

    hi shalinik ,

     

    create a blank query. Copy and paste the below code into the advanced editor:

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ndG7CoMwFAbgVwmZBc1N7VikQ5dO3cTBmiMEbCw2QunTN+kl0nqhmOEny5f/hJPnmIaEhTSiDAf4sLXBI3eIvR0zl5ykNve6anoJuAiehH8IjV5nMyt3t2l5Xy0Xxpwj5F3GvBSDjNl/ZasJdRMQblO3BklowACS/aVRVWlAolNvUN21ZyRVXUMH2iClr6bU1eLjsZ+Hpi7V947EBEk9YSwZr1X8rlV4mQxlXIw/P1U2S4oH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [date = _t, pay_doc_no = _t, doc_number = _t, Instance = _t, amt = _t, comments = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"date", type date}, {"pay_doc_no", type text}, {"doc_number", Int64.Type}, {"Instance", type text}, {"amt", Int64.Type}, {"comments", type text}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "UniqueValue", each Text.Combine({Text.From([doc_number], "en-CA"), Text.From([date], "en-CA"), [Instance]}, ""), type text),
    #"Grouped Rows" = Table.Group(#"Inserted Merged Column", {"UniqueValue"}, {{"Count", each _, type table [date=nullable date, pay_doc_no=nullable text, doc_number=nullable number, Instance=nullable text, amt=nullable number, comments=nullable text, Merged=text]}, {"RowCount", each Table.RowCount(_)= 1, Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([RowCount] = true)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"UniqueValue", "RowCount"}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Removed Columns", "Count", {"date", "pay_doc_no", "doc_number", "Instance", "amt", "comments"}, {"date", "pay_doc_no", "doc_number", "Instance", "amt", "comments"})
in
    #"Expanded Count"

     

    Steps:

    1. Merged columns date, instance and doc number

    2. Grouped by this merged column, ("Count") a column with a table of all rows and another column ("RowCount") to count rows.

    3. Filter the row count column for TRUE.

    4. Removed all columns except "Count"

    5. Expanded count.

     

    Output;

     

     

     

    file:

    Delete rows based on conditions.pbix

    • shalinik's avatar
      shalinik
      Frequent Visitor
      3 years ago

      Hi Avinash, 

      I have another issue , if I have only one row and pay_doc_no satrt with 2 or 1 then we have to exclude row from report as document paid .

      Thanks You..

  • shalinik's avatar
    shalinik
    Frequent Visitor
    3 years ago

    Hi Avinash , Thanks for response .

    this is not working for one condition . like below green highlighted documents. As I can see pay_doc_no is showing NA .so  it should available in report for both date (16, 17).