How create additional table based on specific status

You want to create another table in Power BI that:

• Starts from your claims table.
• Keeps all ClaimSeriesNumber values.
• BUT: if a ClaimSeriesNumber has duplicates, then keep only those rows where Status = "Refiled".

In Power BI you have two options:

• Power Query (M) → for a physical table in the model.
• DAX (calculated table) → also a physical table, but created with DAX formulas.

A. Power Query (M)

1. In Power BI Desktop → Transform Data.
2. Duplicate your claims table.
3. Group by ClaimSeriesNumber (choose All Rows).
4. Add a custom column logic:
   

   * If the grouped table has only one row → expand all rows (keep it).

   If the grouped table has >1 rows → filter it down to Status = "Refiled".

5. Expand back to a flat table.

M code pattern:

let
Source = Claims,
Grouped = Table.Group(Source, {"ClaimSeriesNumber"},
{{"AllRows", each _, type table [ClaimSeriesNumber=..., Status=...]}}),
AddFiltered = Table.AddColumn(Grouped, "Filtered", each
if Table.RowCount([AllRows]) = 1
then [AllRows]
else Table.SelectRows([AllRows], each [Status] = "Refiled")
),
RemoveOthers = Table.RemoveColumns(AddFiltered,{"AllRows"}),
Expanded = Table.ExpandTableColumn(RemoveOthers,"Filtered",{"ClaimSeriesNumber","Status"})
in
Expanded

That gives you exactly the table you want.

B. DAX (Calculated Table)

If you prefer DAX, create a new table:

FilteredClaims =
VAR WithCounts =
ADDCOLUMNS (
Claims,
"SeriesCount", CALCULATE ( COUNTROWS ( Claims ), ALLEXCEPT ( Claims, Claims[ClaimSeriesNumber] ) )
)
RETURN
FILTER (
WithCounts,
-- Keep all singletons
[SeriesCount] = 1
||
-- If duplicates, keep only Refiled
( [SeriesCount] > 1 && Claims[Status] = "Refiled" )
)

This gives you a new physical table in the model.

Result:

• If a ClaimSeriesNumber appears only once → that row stays regardless of status.
• If it appears multiple times → only the “Refiled” rows remain.

I hope it will help