Forum Discussion
Adding Custom Column and pulling values from other rows
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.
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.
4 Replies
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.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.
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.This is just what I was looking for. Thank you!
