Forum Discussion
IF statements to create a baseline
Hi everyone,
I have a dataset with ProductIDs, Subjects and the rating of life for that product. Every ProductID can only have 1 subject named 'Task A', but it can have multiple of the other subjects; Task B, Task C, etc.
I used the IF statement to create a column that looks for and returns the rating of life for Task A using the Add conditional column.
However, I would like to have that new column to also fill in the blanks with that same Task A value based on the ProductID, so that when plotting a line graph I can create a Base line for Task A.
This is an example of how I am trying to make it look like in my data set:
| ProductID | Subject | Product rating of life | Task A Baseline |
| PID_0001 | Task A | 9 | 9 |
| PID_0001 | Task B | 7 | 9 |
| PID_0001 | Task B | 6 | 9 |
| PID_0001 | Task C | 7 | 9 |
| PID_0002 | Task A | 8 | 8 |
| PID_0002 | Task B | 4 | 8 |
| PID_0002 | Task C | 5 | 8 |
| PID_0002 | Task C | 6 | 8 |
| PID_0002 | Task B | 7 | 8 |
| PID_0003 | Task A | 2 | 2 |
| PID_0003 | Task C | 4 | 2 |
Anonymous you can easily do this in power query, add a custom column with the following expression
if [Subject] = "Task A" then [Product rating of life] else nulland then from the menu, use fill down to fill the null values and that will do it.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
4 Replies
- parry2k
Super User
Anonymous you can easily do this in power query, add a custom column with the following expression
if [Subject] = "Task A" then [Product rating of life] else nulland then from the menu, use fill down to fill the null values and that will do it.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
parry2k Thank you for the quick response.
I want to use something else than fill down since the ProductIDs are not sorted. We keep adding new lines in our system so when we export the excel file from our database we will not have the ProductIDs sorted.
I am trying to use some sort of query that uses the fill down function but looks at the ProductIDs in the whole document to know where to fill in the Task A value.
- parry2k
Anonymous you can add a step for sorting before filling down and that will take care of it.