Fill in blanks based on existing data

[jonbox]

I'm trying to fill in some blanks on the below "Probability Adjusted" column.

You can see for each project, "system upgrade", "refresh" and "building work" there are gaps under probability adjusted.

Essentially what i want is a measure that assigns the probability adusted based on the project name, e.g. where there are missing gaps under probability adjusted, i want those to be filled in with the probability adjusted of that project.

So row 3 should have probability adjusted 90%, row 6 100%, row 9 50%.

I'm not sure on the logic though.

Any help please?

Project Name	Type	Predicted Spend	Actual Spend	Probability Adjusted
System Upgrade	Complete Spend	100	100	90%
System Upgrade	Predicted Spend	100	100	90%
System Upgrade	Current Spend	200	200
Refresh	Complete Spend	300	300	100%
Refresh	Predicted Spend	200	200	100%
Refresh	Current Spend	100	100
Building Work	Complete Spend	200	200	50%
Building Work	Predicted Spend	200	200	50%
Building Work	Current Spend	400	400

[Samarth_18]

Hi @jonbox ,

You could try fill up/down option in Power BI. Please refer below article.

https://docs.microsoft.com/en-us/power-query/fill-values-column 

Thanks,

Samarth

[jonbox]

Hi Samarth,

Does this allow to fill based on name of project? since in my above example the probability adjusted is different for each project.

[Samarth_18]

@jonbox It should. Please refer the attached file below.

[jonbox]

Hi, i tried to replicate what you have created in the file but have a problem.

the probability adjusted seems to fill down from another source

the picture below should show a probability adjusted for  90 like it does at the bottom of the image (the 90 is source data). however for ALL projects in my file the probability adjusted is 25. 

the forumla i use:

= Table.FillDown(#"Changed Type",{"Prob_adj"})

[v-xiaotang · Accepted solution]

Hi @jonbox 

Here are 2 ways for your reference,

(1) If you want to create a measure

Measure = 
var _value= CALCULATE(MAX('Table'[Probability Adjusted]),FILTER(ALL('Table'),'Table'[Project Name]= MIN('Table'[Project Name]) && NOT(ISBLANK('Table'[Probability Adjusted]))))
return IF(ISBLANK( MIN('Table'[Probability Adjusted])),_value, MIN('Table'[Probability Adjusted]))

(2) Power Query Editor - Transform - Fill - Down,

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.