Creating a New Column Based on Row Comparison

[Cadbankingjj]

I would like to create a new conditional column on the data below that would be called "Pass To" to normalize this file to others that I am using.

Currently in Power BI - I have created a conditional column called Recall From that is used if the first column is  "B" and provides the information in column C if yes and null if no.  

I would also like to add a column called "Pass To" that would apply only when the first column is "L" and would be compared to other rows to see if columns D,E,F all match.  If they all match I would like the information to be filled  and if so to append the first line (as the transactions are related); and then delete the row.

Based on the data below, I would expect State Street to be filled in on Row 2 below in the "Pass To" column ; and then Row 3 deleted (since the cusip, description and recall date all match); and on row 4 we would expect to have the information from row 11 in the "Pass To" and then have row 11 deleted.  Rows 5-10 should just have the "Pass To" column filled in as they have no duplicates.

Initital:

Expected Result:

I was just wondering the best method to handle this issue.

Thank you,

JJ

[Anonymous · Accepted solution]

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

[Anonymous · Accepted solution]

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.

[Cadbankingjj]

Thank you so much for the help!

[Anonymous · Accepted solution]

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

[Cadbankingjj]

Thank you for the detailed reply; I just have a follow-up question if that's ok.  Where did the Group_Rank in the second part of creating a calculated column in step 2 come from?

Thank you,

JJ