Forum Discussion

Polly's avatar
Polly
Icon for Helper I rankHelper I
8 years ago
Solved

Aggregating data within an IF statement Power Query

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!

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

2 Replies

  • MarcelBeug's avatar
    MarcelBeug
    Icon for Community Champion rankCommunity Champion

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