![]() Then the grouping by doing a generic sum grouping using the UI, then changing the sum function into List.Combine as you showed. Source = Excel.CurrentWorkbook())įirst a simple "Add custom column", with the List.Number function. I like to use the Power Query UI as much as possible, and also go in gradual steps so I can understand afterwards what I did, so I ended up with this to make the lists: let So I'm hoping there's a formula for Baklan You've been of great help, thank you! I managed to do it. I found a mathematical explanation here, but it doesn't seem obvious to me to get this done in Excel either. In my case, the range of values (left column) goes from 1 to 53 for 10.000 products, and the frequencies (right column) go up to thousands per value, so we're talking about 10.000 lists of 100's of thousands of numbers each. You can then use the DAX PERCENTILE function (I understand there is no equivalent in M) on such an "expanded" list.ġ) I don't see an easy way to convert the dataset into the "expanded" dataset in M or DAX (in Excel there's this "hack")Ģ) Even if I would, in real life the expanded dataset would be humungous - and this only to come to a few crunched numbers in the end. To calculate percentiles, you would line the results up as follows: ![]() The problem is simple: I have a frequency table, and want to calculate percentiles. ![]() Am I missing something or is this just something that's not incorporated? This is a quite common problem to solve in statistics it seems to me, but I can't find an easy solution in Excel.
0 Comments
Leave a Reply. |