Flagging items in a group based on criteria

[CharC]

Hi, I'm new to Power Query would like to know how to flag the desired item in a group based on criteria.

My dataset is like below. For each name, data is sorted by date (ascending).

I want to flag:

- For the names with at least one 'yes' in 'meeting certain criteria' column, flag the first occurrence with 'yes'. (see Alice, Jimmy, Sarah)

- For the names with no 'yes' in 'meeting certain criteria', flag the first occurrence with 'no'. (see Simon and Clare)

name	date	meeting certain criteria	Flag
Alice	15/07/2025	No	0
Alice	16/07/2025	Yes	1
Alice	17/07/2025	Yes	0
Simon	15/07/2025	No	1
Jimmy	15/07/2025	Yes	1
Jimmy	16/07/2025	Yes	0
Clare	16/07/2025	No	1
Clare	17/07/2025	No	0
Sarah	15/07/2025	Yes	1
Sarah	16/07/2025	No	0
Sarah	17/07/2025	Yes	0

[Omid_Motamedise]

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

[v-ssriganesh]

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.

[p45cal]

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

[v-ssriganesh]

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.

[bhanu_gautam · Accepted solution]

@CharC 

Load your data into Power Query: Start by loading your dataset into Power Query.

Sort the data: Ensure your data is sorted by name and date in ascending order.

Add an Index Column: This will help in identifying the first occurrence.

Go to the Add Column tab.
Click on Index Column and choose From 0.
Group by Name: Group the data by name to process each group separately.

Go to the Home tab.
Click on Group By.
Group by name and add an aggregation for All Rows.
Add Custom Column for Flagging: Add a custom column to flag the desired rows.

Click on Add Column -> Custom Column.
Use the following formula to create the flag:

let
Source = [All Rows],
HasYes = List.Contains(Source[meeting certain criteria], "Yes"),
Flagged = if HasYes then
Table.AddColumn(Source, "Flag", each if [meeting certain criteria] = "Yes" and [Index] = List.Min(Table.SelectRows(Source, each [meeting certain criteria] = "Yes")[Index]) then 1 else 0)
else
Table.AddColumn(Source, "Flag", each if [Index] = List.Min(Source[Index]) then 1 else 0)
in
Flagged

Expand the Grouped Data: Expand the grouped data to get back to the original table structure with the new Flag column.

Click on the expand icon next to the Custom column.
Select all columns except the Index column.
Remove the Index Column: If you no longer need the index column, you can remove it.

Right-click on the Index column and select Remove.
Load the Data: Load the transformed data back to your worksheet or data model.

[CharC]

Thank you very much. Your solution is easy to follow and produces the correct results.