Forum Discussion
Flagging items in a group based on criteria
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
FlaggedExpand 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.
Super UserUse 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