Hi ,
I need help to calculate a median value across several rows of data using a Power Query formula
My data is set out as follows:
Wendy Jim Mary Tom
6 4 4 5
The median of these numbers is 4.5 . I would to insert another column in Power Query that calculates this median value. I.e.
Wendy Jim Mary Tom Median
6 4 4 5 4.5
I am aware there is a Aggregate function available in Power Query but I think this incorrectly calculates the median as 4.
Hi Rachael,
You can add a column for Median by first selecting the columns for Wendy, Jim, Mary and Tom and then on the Add Column tab > Statistics > Median.
However, you'll see that it also returns 4 as the median. This is by design, but most would agree it's incorrect. You can read other comments about this 'bug' here: https://social.technet.microsoft.com/Forums/en-US/6daed850-c4f5-495b-8ac4-41f5abbc2b55/median-function?forum=powerquery
For now you're best to do your median calculation in Excel.
Kind regards,
Mynda
Hi Mynda, thanks for this answer.
Although not the answer I wanted it has stopped me wasting time trying to find a solution that doesn't exist. I have commented on the social.technet site. This was acknowledged as a "bug" in 2014. A bit annoying that it hasn't been resolved yet.
