IF statements to create a baseline

[Anonymous]

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

[parry2k · Accepted solution]

@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 null

and 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!

[Anonymous]

@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.

[Anonymous]

Thank you @parry2k that solved my problem!