Delete rows with a condition in PowerQuery

[DaniRoAzpi]

I have a database, in which, I have several columns:

1.-Batch.

2.-Parameter.

3.-Inspection date.

A batch can have several parameters and each parameter can have a different inspection date. I would like to delete all the rows of batches that are missing one or more inspection dates, that is, if a batch is missing the inspection date even if it is for a single parameter, all the corresponding rows for that batch should be deleted, even those that do have an inspection date. 

Thanks in advance.

[Rickmaurinus · Accepted solution]

Hey Dani,

Thanks for the question. It's a common scenario that can be a little tricky. Here's a way: 
 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZFLCsAgDETvknUFzUftWYp36LLHL65My0hcSXg4eeN10TNPoYPueeaFM0vKLbHROP4Ae+AEgESARoB5IAOgbl5gZFEEAN6iGAA+Fh0AGkVYFFGjiBZpdgeIK0pgDwYA3uwgqAevKbAHFGFRxOwBjJd9T5kXoFCuAYA/D6tXAmPd7KnwQwUAdVOFeiUa4wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Batch = _t, Parameter = _t, #"Inspection date" = (type date)]),
    ChType = Table.TransformColumnTypes(Source,{{"Batch", type text}, {"Parameter", type text}, {"Inspection date", type date}}),
    RowsWithNull = Table.SelectRows(ChType, each [Inspection date] = null),
    BatchesToRemove = List.Distinct( RowsWithNull[Batch] ),
    BackToSource = Source,
    RemoveRows = Table.SelectRows(BackToSource, each not List.Contains( BatchesToRemove, [Batch] ) )
in
    RemoveRows

You could try above steps. You would

1. Filter your data only the items with null

2. Retrieve the batches related to these

3. Filter the original dataset to exclude these

Cheers,

Rick

--------------------------------------------------

@ me in replies or I'll lose your thread

Master Power Query M? -> https://powerquery.how

Read in-depth articles? -> BI Gorilla

Youtube Channel: BI Gorilla

If this post helps, then please consider accepting it as the solution to help other members find it more quickly.

[Vijay_A_Verma]

Can you please post some dummy data and explain with respect to that dummy data?

[DaniRoAzpi]

Yes, no problem.

This could be an example of my data:

Batch	Parameter	Inspection date
xxxxx1	ppppp1	25/07/2023
xxxxx1	ppppp2	29/07/2023
xxxxx1	ppppp3	29/07/2023
xxxxx1	ppppp4	29/07/2023
xxxxx1	ppppp5	20/07/2023
xxxxx1	ppppp6	29/07/2023
xxxxx2	ppppp1	13/07/2023
xxxxx2	ppppp2	15/07/2023
xxxxx2	ppppp3	28/07/2023
xxxxx2	ppppp4	13/07/2023
xxxxx2	ppppp5	15/07/2023
xxxxx2	ppppp6	28/07/2023
xxxxx2	ppppp7	29/07/2023
xxxxx2	ppppp8	30/07/2023
xxxxx3	ppppp1	15/07/2023
xxxxx3	ppppp2	28/07/2023
xxxxx3	ppppp3	13/07/2023
xxxxx3	ppppp4	15/07/2023
xxxxx3	ppppp5	28/07/2023
xxxxx3	ppppp6
xxxxx3	ppppp7	02/08/2023
xxxxx4	ppppp1	17/07/2023
xxxxx4	ppppp2
xxxxx4	ppppp3
xxxxx4	ppppp4	28/07/2023
xxxxx4	ppppp5	13/07/2023
xxxxx4	ppppp6	15/07/2023
xxxxx4	ppppp7

My goal with this dummy data would be to delete ALL the rows corresponding batch xxxxx3 (it has 1 missing inspection date) and batch xxxxx4, as it has 3 missing inspection dates.

Many thanks.

[Vijay_A_Verma]

Insert this statement where #"Changed Type" should be replaced with your previous step

= Table.Combine(List.Select(Table.Group(#"Changed Type", {"Batch"}, {{"All", each _}})[All], (x)=> not List.Contains(x[Inspection date], null)))

Complete code in action here

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZFLCsAgDETvknUFzUftWYp36LLHL65My0hcSXg4eeN10TNPoYPueeaFM0vKLbHROP4Ae+AEgESARoB5IAOgbl5gZFEEAN6iGAA+Fh0AGkVYFFGjiBZpdgeIK0pgDwYA3uwgqAevKbAHFGFRxOwBjJd9T5kXoFCuAYA/D6tXAmPd7KnwQwUAdVOFeiUa4wU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Batch = _t, Parameter = _t, #"Inspection date" = (type date)]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Batch", type text}, {"Parameter", type text}, {"Inspection date", type date}}),
    #"Grouped Rows" = Table.Combine(List.Select(Table.Group(#"Changed Type", {"Batch"}, {{"All", each _}})[All], (x)=> not List.Contains(x[Inspection date], null)))
in
    #"Grouped Rows"

[ToddChitt]

Is the goal to FILTER records in Power Query based on some criteria (DELETE, in my opinion, is a DATABASE operation, while FILTER is a Power Query operation)?

If your goal is to FILTER and remove any Batch that does not have all the Inspections, then i would do this:

DUPLICATE the query. On the Duplicate, do a FILTER to remove Inspection Dates that are NULL or Blank. Then do a GROUP BY on Batch and COUNT of Rows for the Aggregation. Then Filter the Result for Count = 6. Finally, JOIN that (inner join) back to the original query on Batch.