Creating a New Column Based on Row Comparison

Hi  Cadbankingjj ,

Here are the steps you can follow：

1.In Power Query -- add Column – Index Column – From 1.

2. Create calculated column.

Count =
COUNTX(
    FILTER(ALL('Table'),
     'Table'[CUSIP]=EARLIER('Table'[CUSIP])&&'Table'[Security Desc]=EARLIER('Table'[Security Desc])&&'Table'[Recall Date]=EARLIER('Table'[Recall Date])),[B/L])

Pass To =
IF(
    [Count]=2 &&[Group_Rank]=1,
MAXX(
    FILTER(ALL('Table'),
  'Table'[CUSIP]=EARLIER('Table'[CUSIP])&&'Table'[Security Desc]=EARLIER('Table'[Security Desc])&&'Table'[Recall Date]=EARLIER('Table'[Recall Date])&&'Table'[Group_Rank]=EARLIER('Table'[Group_Rank])+1),
  [Contra Desc]),
  IF(
    [Count]<>2,[Contra Desc],BLANK()))

3. Create calculated table.

Table 2 =
var _table=
FILTER('Table',[Pass To]<>BLANK())
return
SUMMARIZE(
    _table,[B/L],[Contra],[Contra Desc],[CUSIP],[Security Desc],[Recall Date],[Buy-in Date],[Recall Qty],[Pass To],[Index])

4. Result:

Rows of the same data in the calculation table are automatically aggregated, and we need an Index to distinguish them

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Hi  Cadbankingjj ,

"Sorry, I forgot to include [Group_Rank].

[Group_Rank] is calculated by grouping based on [CUSIP], [Security Desc], and [Recall Date], and then determining the corresponding sorting for [Index]."

Group_Rank =
RANKX(
    FILTER(ALL('Table'),
    'Table'[CUSIP]=EARLIER('Table'[CUSIP])&&'Table'[Security Desc]=EARLIER('Table'[Security Desc])&&'Table'[Recall Date]=EARLIER('Table'[Recall Date])),[Index],,ASC)

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.