List.Median
The middle value of a list once it is sorted, nulls left out.
Group: List. Power Query M has it too: a query that uses it is written as in Power BI.
Syntax
List.Median(list as list, optional comparisonCriteria as any) as any| Parameter | Type | Description |
|---|---|---|
list | list | The values; nulls are skipped. |
comparisonCriteria | any optional | How to order the values: a comparer or a key function. OpenBI does not use it yet and orders the values themselves. |
Returns
any. The middle value; null when there is nothing but null.
Remarks
- With an even count of numbers, durations, times or datetimes, the median is the mean of the two in the middle; of texts, dates or datetimezones it is the lower of the two.
- A NaN among the numbers makes the median NaN.
Compared with Power Query
Power Query orders the values by comparisonCriteria when it is given; OpenBI does not use it yet.
Examples
A typical basket
List.Median({120, 80, 95, 300, 100})100One large basket of 300 does not pull the median up, as it would the average.
An even count
List.Median({80, 95, 100, 300})97.5A typical call length
List.Median({#duration(0, 0, 10, 0), #duration(0, 0, 20, 0)})#duration(0, 0, 15, 0)Between ten and twenty minutes: the mean of the two.