Forum Discussion

DaniRoAzpi's avatar
DaniRoAzpi
New Member
3 years ago
Solved

Delete rows with a condition in PowerQuery

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.

Tips and Tricks
  • Rickmaurinus's avatar
    Rickmaurinus
    3 years ago

    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.

5 Replies

  • Rickmaurinus's avatar
    Rickmaurinus
    Helper V
    3 years ago

    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's avatar
    Vijay_A_Verma
    Most Valuable Professional
    3 years ago

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

    • DaniRoAzpi's avatar
      DaniRoAzpi
      New Member
      3 years ago

      Yes, no problem.

      This could be an example of my data:

      BatchParameterInspection date
      xxxxx1ppppp125/07/2023
      xxxxx1ppppp229/07/2023
      xxxxx1ppppp329/07/2023
      xxxxx1ppppp429/07/2023
      xxxxx1ppppp520/07/2023
      xxxxx1ppppp629/07/2023
      xxxxx2ppppp113/07/2023
      xxxxx2ppppp215/07/2023
      xxxxx2ppppp328/07/2023
      xxxxx2ppppp413/07/2023
      xxxxx2ppppp515/07/2023
      xxxxx2ppppp628/07/2023
      xxxxx2ppppp729/07/2023
      xxxxx2ppppp830/07/2023
      xxxxx3ppppp115/07/2023
      xxxxx3ppppp228/07/2023
      xxxxx3ppppp313/07/2023
      xxxxx3ppppp415/07/2023
      xxxxx3ppppp528/07/2023
      xxxxx3ppppp6 
      xxxxx3ppppp702/08/2023
      xxxxx4ppppp117/07/2023
      xxxxx4ppppp2 
      xxxxx4ppppp3 
      xxxxx4ppppp428/07/2023
      xxxxx4ppppp513/07/2023
      xxxxx4ppppp615/07/2023
      xxxxx4ppppp7 

       

      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.

      • ToddChitt's avatar
        ToddChitt
        Super User
        3 years ago

        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.