Delete rows based on conditions

[shalinik]

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.

[shalinik]

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).

[adudani]

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]

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..