Forum Discussion
Update column based on 2 other columns
I have a report that is using row level security. The team lead I made the dashboard for has requested I restrict a certain portion of the data. I can do this easily by changing the values in the column I base RLS on, but I need to do it depending on 2 different vairiables.
Essentially I need to either update the values in the existing column, or create a new column that is something along the lines of:
= Table.AddColumn(#"Renamed Columns", "Updated Sort", each if [Account] = "12345" and [Cost Center No] = "67890" then "Remove" else {"Original Sort})
This formula isn't working for me, where I am trying to create a new column that populates the value as "Remove" is the value in the Account column is 12345 and the value in the Cost Center No is 67890. If it is not both, then use the value in column Original Sort.
An added layer of complexity is that I need this for 4 different Cost Center No values, though all the same Account, hopfully without creating 4 different new columns.
Hi z-rising ,
ryan_mayu Thanks for your concern about this case!
And In addition to what ryan_mayu said, I would like to remind you that you are using "12345" and "67890" in your code, I am not sure what kind of data you have, but the addition of " " means it is a text type column. If you say that these two columns should be of type Number, then you need to remove the "".
About "An added layer of complexity is that I need this for 4 different Cost Center No values, though all the same Account, hopfully without creating 4 different new columns." Can you show us your data and the expected results? Maybe we can help you find a quicker solution.
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi z-rising ,
ryan_mayu Thanks for your concern about this case!
And In addition to what ryan_mayu said, I would like to remind you that you are using "12345" and "67890" in your code, I am not sure what kind of data you have, but the addition of " " means it is a text type column. If you say that these two columns should be of type Number, then you need to remove the "".
About "An added layer of complexity is that I need this for 4 different Cost Center No values, though all the same Account, hopfully without creating 4 different new columns." Can you show us your data and the expected results? Maybe we can help you find a quicker solution.
Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.