Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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.
Solved! Go to 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.
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.
This is just what I was looking for. Thank you!
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.
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.
User | Count |
---|---|
96 | |
85 | |
77 | |
66 | |
63 |
User | Count |
---|---|
110 | |
95 | |
95 | |
64 | |
59 |