Strange Filter Situation Power Query

[Anonymous]

I admit I'm a little lost with where to begin with something I'm trying to do in power query.

I made up some dummy data to imitate the problem I'm trying to solve. 

My boss wants me to only keep rows like the following:

- Insect Description is Mantis

- Insect description is Moth

- Insect Description is Caterpillar AND Caterpillar is the only Insect Description for that Insect ID AND there is a date in the Finish column on that row (for example, see Insect ID 5, 8, 15, etc)

To make it more clear which rows my boss would want me to keep, I have made a new column "Want to Keep This Row" and manually put an x in the rows my boss would want. However, the original data set is hundreds of rows long and I'm wondering if there would be a more feasible way than for me to manually add an x.

I know how to write m code to filter for the Moth and Mantis Insect Description, but I'm struggling with isolating the Caterpillar rows. This is because my boss only wants the caterpillar rows where there is a finish date and also where caterpillar is the only insect description for the insect ID. For example he wouldn't want the caterpillar row for insect id 9 because that same insect ID also has a cocoon row, nor would he want the caterpillar rows on insect IDs that also have moth rows--but he does want all of the moth rows!

Does this description make sense? What would you advise?

Insect ID	Insect Description	Start	Finish	Insect Value	Want to Keep this Row
1	Caterpillar	04/06/2021	04/13/2021	$        2,692.00
1	Cocoon	04/13/2021	04/13/2021	$        2,692.00
1	Moth	04/13/2021	04/13/2021	$        2,692.00	x
2	Caterpillar	02/04/2021	02/06/2021	$      17,127.00
2	Cocoon	02/06/2021	02/06/2021	$      17,127.00
2	Moth	02/06/2021	02/06/2021	$      17,127.00	x
3	Mantis	10/26/2021	11/01/2021	$          800.00	x
4	Mantis	9/1/2021	10/15/2021	$         900.00	x
5	Caterpillar	06/08/2021	06/09/2021	$      15,474.00	x
6	Caterpillar	06/15/2021		$      28,760.00
7	Caterpillar	03/09/2021	03/11/2021	$        1,426.00	x
8	Caterpillar	06/24/2021	06/30/2021	$        4,063.00	x
9	Caterpillar	06/16/2021	06/20/2021	$        5,617.00
9	Cocoon	06/20/2021		$        5,617.00
10	Mantis	3/5/2021	5/1/2021	$      1,000.00	x
11	Mantis	4/28/2021	5/15/2021	$    11,000.00	x
12	Mantis	6/14/2021	7/30/2021	$          50.00	x
13	Mantis	8/21/2021	10/1/2021	$          80.00	x
14	Mantis	7/29/2021	8/14/2021	$      1,000.00	x
15	Caterpillar	04/05/2021	04/08/2021	$        1,726.00	x
16	Caterpillar	01/25/2021	02/01/2021	$      29,966.00
16	Cocoon	02/01/2021	02/01/2021	$      29,966.00
16	Moth	02/01/2021		$      29,966.00	x
17	Mantis	2/15/2021	3/4/2021	$         900.00	x
18	Caterpillar	01/22/2021	01/27/2021	$      18,532.00	x
19	Caterpillar	03/23/2021	03/24/2021	$        2,800.00
19	Cocoon	03/24/2021	03/24/2021	$        2,800.00
19	Moth	03/24/2021	03/24/2021	$        2,800.00	x
20	Caterpillar	08/14/2021	08/21/2021	$      15,654.00
20	Cocoon	08/21/2021	08/21/2021	$      15,654.00
20	Moth	08/21/2021	08/21/2021	$      15,654.00	x

[lbendlin · Accepted solution]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZVNagMxDIWvMgxdmlqS/9dd9wQhi1AKDYSkpFnk+FWaGSPFbsHNKgp8z3rys7LZzDib+WV3eT9/7g+H3Zkr8BaiJSC8F+jWYnqalg+ZWOgZ4PbjvDWLzOntdDo+QiMKr6fLxzh//eGpNUKW4VWGhKtVBpNBSrILUj4kMyCw2hjC7y7cDd8dL/sv/oJgqTKIFrCdwzRlAKngpUKxFWExDB2+aDy0Y4wWcrXBRWlsBOOTlyqxpyKOFzBlkyLIEaYWduJULrAzCDSeouwh93ogL5w4aGW8geikTOlaiUKGOjLBRFS5KCpYCvobRJAX6mwdYrDNFNCAvk1ECbP3LOiHNGBLk6TZdR1e6s6Ou9e8SjMfrcLYz7LiVZaTpZqBLHr53Xwny7zbqu1bkXtBSjpI2Ekzdx/k027cUDElRnWR8XG34H8UxHLB7nsS2HXhkpwjiZt3thljuxKw85L4aKrdc5Ga28gmOLWgsfOSeLE78a6p0w2ZuuAmoVPnKKkhiXWQYwLLnw20XmQmQYZdrMkYvPqzAGVFQiMKq5Mhno1svwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Insect ID" = _t, #"Insect Description" = _t, Start = _t, Finish = _t, #"Insect Value" = _t, #"Want to Keep this Row" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Single", each let s=Table.SelectRows(Source,(k)=>[Insect ID]=k[Insect ID]) in Table.RowCount(s)=1 and s[Insect Description]{0}="Caterpillar"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each [Insect Description] <> "Cocoon" and [Finish] <> " " and ([Insect Description] <> "Caterpillar" or [Single] = true))
in
    #"Filtered Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

[Anonymous]

Thank you so much for your fast response! In my case, I am using power query with an Excel workbook and it looks like you are referencing a JSON object. Would I add your code like below if I wanted to use it with my Excel table?

let
    Source = 
          Table.FromRows(
               Excel.CurrentWorkbook(){[Name="Table1"]}[Content], 
               let _t = ((type nullable text) meta [Serialized.Text = true]) in type table 
              [#"Insect ID" = _t, 
                    #"Insect Description" = _t, 
                    Start = _t, 
                    Finish = _t, 
                    #"Insect Value" = _t, 
                    #"Want to Keep this Row" = _t]
          ),
    #"Added Custom" = 
        Table.AddColumn(
            Source, 
            "Single", 
            each 
                let s=Table.SelectRows(
                    Source,
                    (k)=>[Insect ID]=k[Insect ID]
                ) in Table.RowCount(s)=1 
                    and s[Insect Description]{0}="Caterpillar"
            ),
    #"Filtered Rows" = 
        Table.SelectRows(
            #"Added Custom", 
                each 
                    [Insect Description] <> "Cocoon" and 
                        [Finish] <> " " and 
                        ([Insect Description] <> "Caterpillar" 
                            or [Single] = true
                        )
            )
in
    #"Filtered Rows"

I did notice that I got the below error when I used this code. What would you recommend?

"Expression.Error: We cannot convert a value of type Table to type List.
Details:
Value=[Table]
Type=[Type]"

When I asked it to show me the error, it showed me the Source step.

[lbendlin]

Use your excel table as the source and then manually add the other two transforms.