I am looking to filter data based on multiple conditions across different column

Question

Let's say I have this fictional table. This table shows me a fictional list of customers and the various claim type for the services they have decided to have, which outlet they went to, if they are a mammal or bird.

Outlet	Name	Claim Type	Mammal/Bird	StatusToday	Date of Visit
North	Cat	Nails Trim	M	Paid	1/1/2023
North	Cat	Nails Polish	M	Rejected	2/1/2023
North	Cat	Second session	M	Paid	10/1/2023
North	Cat	Fur brush	M	Appeal	11/1/2023
North	Cat	Fur brush	M	Rejected	11/1/2023
North	Cat	Fur brush	M	Paid	10/1/2023
South	Cat	Dance class	M	Appeal	15/1/2023
North	Cat	Dance class	M	Approved	30/1/2023
East	Dog	Nails Trim	M	Approved	1/1/2023
East	Dog	Second session	M	Approved	2/1/2023
South	Rabbit	Second session	M	Rejected	2/1/2023
South	Parrot	Feather brush	B	Approved	5/1/2023
West	Parrot	Egg Care	B	Approved	1/2/2023
West	Cat	Egg Care	M	Rejected	6/2/2023
North	Dog	Nails Trim	M	Rejected	2/2/2023

First, my task was to look for customers who has either paid or/and approved, with investigate or/and rejected. This was achieveable with the add custom column via Power Query, which look something like

let
    customer = [Name],
    statusList = Table.SelectRows(#"Previous Step", each [Name] = customer)[StatusToday],
    paid = List.Contains(statusList, "Paid"),
    rejected = List.Contains(statusList, "Rejected"),
    approved = List.Contains(statusList, "Approved"),
    appealed = List.Contains(statusList, "Appealed"),
    allPaid = List.AllTrue(List.Transform(statusList, each _ = "Paid")),
    allRejected = List.AllTrue(List.Transform(statusList, each _ = "Rejected")),
    allApproved = List.AllTrue(List.Transform(statusList, each _ = "Approved")),
    allAppealed = List.AllTrue(List.Transform(statusList, each _ = "Appealed"))
in
    if ((rejected or appealed) and (paid or approved)) then "Has rejection/appeal with paid/approved"
    else if allPaid and not (approved or rejected or appealed) then "Paid All"
    else if allRejected and not (paid or approved or appealed) then "Rejected All"
    else if allApproved and not (paid or rejected or appealed) then "Approved All"
    else if allAppealed and not (paid or rejected or approved) then "Appealed All"
    else "Unknown Status"

So, it will look something like

Outlet	Name	Claim Type	Mammal/Bird	StatusToday	Date of Visit	Custom
North	Cat	Nails Trim	M	Paid	1/1/2023 0:00	Has rejection/appeal with paid/approved
North	Cat	Nails Polish	M	Rejected	2/1/2023 0:00	Has rejection/appeal with paid/approved
North	Cat	Second session	M	Paid	10/1/2023 0:00	Has rejection/appeal with paid/approved
North	Cat	Fur brush	M	Appeal	11/1/2023 0:00	Has rejection/appeal with paid/approved
North	Cat	Fur brush	M	Rejected	11/1/2023 0:00	Has rejection/appeal with paid/approved
North	Cat	Fur brush	M	Paid	10/1/2023 0:00	Has rejection/appeal with paid/approved
South	Cat	Dance class	M	Appeal	15/1/2023 0:00	Has rejection/appeal with paid/approved
North	Cat	Dance class	M	Approved	30/1/2023 0:00	Has rejection/appeal with paid/approved
East	Dog	Nails Trim	M	Approved	1/1/2023 0:00	Has rejection/appeal with paid/approved
East	Dog	Second session	M	Approved	2/1/2023 0:00	Has rejection/appeal with paid/approved
South	Rabbit	Second session	M	Rejected	2/1/2023 0:00	Rejected All
South	Parrot	Feather brush	B	Approved	5/1/2023 0:00	Approved All
West	Parrot	Egg Care	B	Approved	1/2/2023 0:00	Approved All
West	Cat	Egg Care	M	Rejected	6/2/2023 0:00	Has rejection/appeal with paid/approved
North	Dog	Nails Trim	M	Rejected	2/2/2023 0:00	Has rejection/appeal with paid/approved

This custom column works, however, I have a huge list of data to work with (24,000~) and I would like to make it faster. In addition, I was asked to futher filter the data, where I look for the same or similar claim type, because sometimes, the salesperson submited the wrong claim.

For example, Cat, being a mammal, can only have services for "Nails Trim" not "Nail Polish". It is assumed that the data was entered wrongly because the first word is similar. So for the "rejected" claim, it is closed, and traditionally the table would then look like this (i am not adovcating putting nail polish on birds, this is a fictional table.)

Name	Claim Type	Mammal/Bird	StatusToday	Date of Visit	Custom	Case Status	Reasons
Cat	Nails Trim	M	Paid	1/1/2023 0:00	Has rejection/appeal with paid/approved	Closed
Cat	Nails Polish	M	Rejected	2/1/2023 0:00	Has rejection/appeal with paid/approved	Closed	Ineligible
Cat	Second session	M	Paid	10/1/2023 0:00	Has rejection/appeal with paid/approved	Closed
Cat	Fur brush	M	Appeal	11/1/2023 0:00	Has rejection/appeal with paid/approved	Closed	Mulitple claim
Cat	Fur brush	M	Rejected	11/1/2023 0:00	Has rejection/appeal with paid/approved	Closed	Mulitple claim
Cat	Fur brush	M	Paid	10/1/2023 0:00	Has rejection/appeal with paid/approved	Closed
Cat	Dance class	M	Appeal	15/1/2023 0:00	Has rejection/appeal with paid/approved	Closed	Mulitple claim
Cat	Dance class	M	Approved	30/1/2023 0:00	Has rejection/appeal with paid/approved	Closed
Dog	Nails Trim	M	Approved	1/1/2023 0:00	Has rejection/appeal with paid/approved All	Closed
Dog	Second session	M	Approved	2/1/2023 0:00	Has rejection/appeal with paid/approved	Closed
Rabbit	Second session	M	Rejected	2/1/2023 0:00	Rejected All	Open
Parrot	Feather brush	B	Approved	5/1/2023 0:00	Approved All	Closed
Parrot	Egg Care	B	Approved	1/2/2023 0:00	Approved All	Closed
Cat	Egg Care	M	Rejected	6/2/2023 0:00	Has rejection/appeal with paid/approved	Closed	Ineligible
Dog	Nails Trim	M	Rejected	2/2/2023 0:00	Has rejection/appeal with paid/approved	Open

Cases that are "open" needs further investigation, and be manually input, which the reasons can be found out from other columns, etc, Cat, not being a bird, isn't eligble for "nail polish". (i am not adovcating putting nail polish on birds, this is a fictional table.)

In the idea world, I would be able to find those claims that are

1. Similar, paid/approved with rejected/appeal

2. Same, paid/approved with rejected/appeal

3. All rejected, all paid, all approved, all appeal...etc

4. For the "Second session", if they stand alone without any services rendered on the date before them and is rejected, to show as "Open" in the custom column

I have tried Pivot Table, but I wasn't able to produce the table I want. Additionally, someone asked me to find the outlets with the most "careless mistakes" (etc, nail polish instead of nail trim for cat), has many "second session" without the first session so we can go down to the store and educate the staff there. Is there another way I can load my custom column without the long waiting time? What excel knowledge do I need to learn so I can fulfilled my current work demands?

Answer 1

So far as picking up errors, as pointed out by @JvDv you will need to develop a robust algorithm to determine which procedures are in error, or at least which to review.

For speeding up the generation of the Custom column, below might be faster.

See the code comments to better understand the algorithm.

I used the Table.Group method and a different method of determining the status comment.

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Outlet", type text}, {"Name", type text}, {"Claim Type", type text}, {"Mammal/Bird", type text}, {"StatusToday", type text}, {"Date of Visit", type date}}),

//Add index column to retain original order
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),

//create Status List
    #"Status List" = List.Buffer({"Paid","Rejected","Approved","Appeal"}),

//Group by Name
    #"Grouped Rows" = Table.Group(#"Added Index", {"Name"}, {
        {"All", each _, type table [Outlet=nullable text, Name=nullable text, Claim Type=nullable text, #"Mammal/Bird"=nullable text, StatusToday=nullable text, Date of Visit=nullable date, Index=number]}}),

//Add custom column to output different statuses
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each 
        let
          Status = List.Intersect({#"Status List", [All][StatusToday]}, Comparer.OrdinalIgnoreCase),
          Result = if List.Count(Status) = 0 then null
                    else if List.Count(Status) = 1 then Status{0} & " All" 
                    else if List.Count(Status) >= 3 then "Has rejection/appeal with paid/approved" 
                    else if List.Count(Status) = 2 
                            then 
                                if Status = {"Paid","Approved"} or Status = {"Rejected","Appeal"} then null else "Has rejection/appeal with paid/approved"
                    else null
      in 
        Result, type text),

//Re-expand grouped table
//Sort back to original order
//delete the Index column
    #"Expanded All" = Table.ExpandTableColumn(#"Added Custom", "All", {"Outlet", "Claim Type", "Mammal/Bird", "StatusToday", "Date of Visit", "Index"}),
    #"Sorted Rows" = Table.Sort(#"Expanded All",{{"Index", Order.Ascending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index"})
in
    #"Removed Columns"

Source Data

Results