Adding Custom Column and pulling values from other rows

[turp111]

I need to add a new column with conditions but also to pull a value from another row if a certain condition is met.  Below is my table and columns, here are my conditions:

• If FEE_TYPE = 2 and SF_FIN_SERV_ID <> null then SF_NAME1
• If FEE_TYPE = 2 and SF_FIN_SERV_ID is null then "NATIONWIDE"
• If FEE)_TYPE = 1 then SF_NAME1 where SF_FIN_SERV_ID = "TP"

The first two are fairly easy for me, it's the last one that's giving me trouble.  Basically, whenever the FEE_TYPE = 1, that value also needs to be THE PENSION DESIGN GROUP.  I just can't seem to figure out the right syntax for this.  I also added an example of what this should look like.

[turp111]

Thank you, this is helpful.  That said, I should have been more clear about the data.  The example I gave here is just a subset.  The SF_NAME1 column has thousands of rows.  There should have been another column named 'PLAN' in my example.  So this subset of data would be for Plan 10.  I've added a new example of what I'm expecting.  So based on your first solution I'm getting the max value in SF_NAME1 for every row that meets that condition when I really need it only for Plan 10, and then a different value for Plan 20, etc.  Sorry for the initial confusion and hopefully this helps.

[turp111]

This is just what I was looking for.  Thank you!

[v-jingzhang · Accepted solution]

Hi @turp111 

Please use this new code:

if [FEE_TYPE] = 2 and [SF_FIN_SERV_ID] <> "" then [SF_NAME1] else if [FEE_TYPE] = 2 and [SF_FIN_SERV_ID] = "" then "NATIONWIDE" else if [FEE_TYPE] = 1 then let vPlan = [PLAN] in List.Max(Table.SelectRows(#"previous step", each [PLAN] = vPlan and [SF_FIN_SERV_ID] = "TP")[SF_NAME1]) else ""

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[v-jingzhang]

Hi @turp111 

You can add a custom column with below code. Modify "Previous Step Name" accordingly. 

if [FEE_TYPE] = 2 and [SF_FIN_SERV_ID] <> "" then [SF_NAME1] else if [FEE_TYPE] = 2 and [SF_FIN_SERV_ID] = "" then "NATIONWIDE" else if [FEE_TYPE] = 1 then List.Max(Table.SelectRows(#"Previous Step Name", each [SF_FIN_SERV_ID] = "TP")[SF_NAME1]) else ""

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.