Group table by specific column and identify if that particular group has a specific value

[jose_cruzrodz]

Greetings,

Here is a sample of my data:

ID	SignedBy	Price
ABC123	Alondra	$2,500
ABC123	Peter	$2,500
ABC123	Roman	$2,500
ZXCV4657	Steven	$100
HJKL4321	John	$100
HJKL4321	Alexa	$100
KMEF8293	Mildred	$47,890
KMEF8293	Maria	$47,890

Expected results:

ID	SignedBy	Price	Status
ABC123	Alondra	$2,500	Passed
ABC123	Peter	$2,500	Passed
ABC123	Roman	$2,500	Passed
ZXCV4657	Steven	$100	N/A
HJKL4321	John	$500	N/A
HJKL4321	Alexa	$500	N/A
KMEF8293	Mildred	$47,890	Failed
KMEF8293	Maria	$47,890	Failed

Compliance rules:

1. If PRICE is >= $2,000 & SIGNEDBY = "Peter" --> STATUS = "Passed"
2. If PRICE is < $2,000 --> STATUS = "N/A"
3. Anything else --> STATUS = "Failed"

 General idea:

• ID is a document, so all rows that belong to the same ID should be evaluated as a group to check if any row of the same group complies or not. For example, for ID ABC123, it has three rows of data, so since one of those rows match compliance rule A, then all rows of ID ABC123 should be labeled as "Passed". Rows of IDs ZXCV4657 and HJKL4321 match compliance rule B, due to the price, so each group should be labeled as "N/A". Rows of ID KMEF8293 failed because although the price is right, Peter did not show up in the SIGNEDBY column, so all rows of the group should be labeled as "Failed".

I have attempted adding a custom column to the main table in Power BI using IF, Countrows, Filter and Earlier, but I have had no luck. If the solution can be built in Power Query, better; but I am open to all your great solutions. Thanks!!! 😁

[KNP · Accepted solution]

Give this code a try:

let
    Source = Table,
    GR1 = Table.Group(
        Source,
        {"ID"},
        {
            {"count", each Table.RowCount(_), Int64.Type},
            {"all", each _, type table [ID = nullable text, SignedBy = nullable text, Price = nullable number]},
            {"max", each List.Max([Price]), type nullable number}
        }
    ),
    AllSignedBy = Table.AggregateTableColumn(GR1, "all", {{"SignedBy", Text.Combine, "SignedBy"}}),
    AddStatus = Table.AddColumn(
        AllSignedBy,
        "Status",
        each
            if [max] >= 2000 and Text.Contains(Text.Lower([SignedBy]), "peter") then
                "Passed"
            else if [max] < 2000 then
                "N/A"
            else
                "Failed",
        type text
    ),
    ROC1 = Table.SelectColumns(AddStatus, {"ID", "Status"}),
    Merge1 = Table.NestedJoin(ROC1, {"ID"}, Source, {"ID"}, "Removed Other Columns", JoinKind.LeftOuter),
    Expand1 = Table.ExpandTableColumn(Merge1, "Removed Other Columns", {"SignedBy", "Price"}, {"SignedBy", "Price"})
in
    Expand1

Table = The source table you provided above. 

It's a little crude and can be improved upon but it should help get you what you're looking for.

[jose_cruzrodz]

Hello. Thanks for the input. It almost worked out of the box! The single modification that I had to do, after an hour or so of looking for what may be wrong and trying tons of stuff, was removing the Text.Lower portion of the AddStatus step. It works great. Now, I am modifying stuff a little bit to just add the Status column to existing main table, which has over 30 more columns. Thanks for your help!!!