Aggregating data within an IF statement Power Query

[Polly]

Hi,

I wonder if anyone can help. 

I am trying to construct a new column. I want it to do:

IF the status is complete     AND the BH_c is null     then take the median of all the BH's and put it in here

ELSE just use BH_c

I am trying this using the following M code

if List.AllTrue(
{ [Status]="Completed", [BH__c]=null }
) then List.Median({[BH__c]})
else [BH__c]

It is valid but where the status is complete     AND the BH is null it displays NULL I think this is becuase it is just looking at the row in question and the median of null is null.

Has anyone got any idea on how to get this to work?

Thanks!

[MarcelBeug · Accepted solution]

Within the List.Median function, you should have <PreviousStep>[BH_1] instead of {[BH_1]}

[Polly]

Thanks @MarcelBeug