Flagging items in a group based on criteria

Hello CharC,
Thank you for reaching out to the Microsoft Fabric Forum Community.

I have reproduced your scenario in Power BI Desktop using the dataset you provided and successfully implemented the flagging logic as per your requirements. The solution flags the first occurrence of "Yes" for names with at least one "Yes" in the "meeting certain criteria" column and the first occurrence of "No" for names with no "Yes" entries, with the data sorted by date (ascending).

Used the following formula:

let

    currentTable = [AllRows],

    HasYes = List.Contains(currentTable[meeting certain criteria], "Yes"),

    AddFlag = Table.AddIndexColumn(currentTable, "Index", 0, 1),

    FlagLogic = Table.AddColumn(

        AddFlag,

        "Flag",

        each

            if HasYes then

                if [meeting certain criteria] = "Yes" and [Index] = List.PositionOf(currentTable[meeting certain criteria], "Yes") then 1

                else 0

            else

                if [meeting certain criteria] = "No" and [Index] = 0 then 1

                else 0

    )

in

    FlagLogic

I have tested the solution, and it produces the expected output matching your example. For your reference, I have attached a .pbix file containing the sample data and the Power Query transformations. You can download it, open it in Power BI Desktop, and review the steps under the Power Query Editor to see how it was implemented.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

Another:

let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    AddedIndex = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    ChangedType = Table.TransformColumnTypes(AddedIndex,{{"date", type date}}),
    TrimmedText = Table.TransformColumns(ChangedType,{{"name", Text.Trim, type text}}),
    GroupedRows = Table.Group(TrimmedText, {"name", "meeting certain criteria"}, {{"MinDate", each List.Min([date]), type nullable date}}),
    PivotedColumn = Table.Pivot(GroupedRows, List.Distinct(GroupedRows[#"meeting certain criteria"]), "meeting certain criteria", "MinDate"),
    MergedQueries = Table.NestedJoin(TrimmedText, {"name"}, PivotedColumn, {"name"}, "Custom1", JoinKind.LeftOuter),
    ExpandedCustom = Table.ExpandTableColumn(MergedQueries, "Custom1", {"No", "Yes"}),
    AddedCustom = Table.AddColumn(ExpandedCustom, "Flag", each if ([meeting certain criteria]="Yes" and [date]=[Yes]) or ([Yes]=null and [meeting certain criteria]="No" and [date]=[No]) then 1 else 0),
    SortedRows = Table.Sort(AddedCustom,{{"Index", Order.Ascending}}),
    ROC = Table.SelectColumns(SortedRows,{"name", "date", "meeting certain criteria", "Flag"})
in
    ROC

Hello CharC,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

Use the following code

let
    Source = YourPreviousStep,
    AddFlag = Table.AddColumn(Source, "WithFlag", each 
        let
            tbl = [AllRows],
            hasYes = List.ContainsAny(tbl[meeting certain criteria], {"Yes"}),
            flagged = if hasYes then
                Table.AddIndexColumn(
                    Table.TransformColumns(tbl, {"meeting certain criteria", Text.Upper}),
                    "RowIndex", 0, 1, Int64.Type
                )
                else
                Table.AddIndexColumn(tbl, "RowIndex", 0, 1, Int64.Type),
            result = Table.AddColumn(flagged, "Flag", each 
                if hasYes then
                    if [meeting certain criteria] = "Yes" and [RowIndex] = 
                        List.Min(
                            Table.SelectRows(flagged, each [meeting certain criteria] = "Yes")[RowIndex]
                        )
                    then 1 else 0
                else
                    if [RowIndex] = 0 then 1 else 0
            ),
            final = Table.RemoveColumns(result, {"RowIndex"})
        in
            final
    ),
    Expand = Table.ExpandTableColumn(AddFlag, "WithFlag", {"date", "meeting certain criteria", "Flag"})
in
    Expand