Forum Discussion
Creating Index within Index
- 6 years ago
Hi, roncruiser
The complete code, I've changed it, you just need to follow the picture below to modify the parameters to achieve your desired effect.
// output let Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content], chType = Table.TransformColumnTypes(Source,{{"Delay", type number}}), fx = (tbl as table, Coarse_start as number, Fine_start as number, Fine_end as number)=> let sortedTbl = Table.Sort(tbl, {"Delay", 0}), rows = List.Buffer(Table.ToRows(sortedTbl)), n = List.Count(rows), gen = List.Generate( ()=>{0, {}, 0},//{counter, {new_list}, delay_counter} each _{0}<=n, each let count = Fine_end+1-Fine_start, Index1 = _{0}, Coarse = Number.IntegerDivide(_{0}, count)+Coarse_start, Fine = Number.Mod(_{0}, count)+Fine_start in {_{0}+1, {Index1, Coarse, Fine}, _{0}}, each List.InsertRange(rows{_{2}}, 4, _{1}) ), toTbl = Table.FromRows( List.Skip(gen), List.InsertRange( Table.ColumnNames(sortedTbl), 4, {"Index1", "Coarse", "Fine"} ) ) in toTbl, group = Table.Group(chType, "Ref", {"t", each fx(_, 3, 4, 11)})[t], result = Table.Combine(group) in result
Glad you have a solution roncruiser - you can mark multiple answers as solutions.
As for Excel needing to be idle, yeah, when it is processing large volumes of data itself - i.e. data that cannot be folded back to a server for processing, you just need to hit refresh and go get a cup of coffee or something. Two things that will greatly speed things up.
- Running Excel 64 bit
- Having at least 16GB of RAM. I've seen modest queries kill an 8GB machine causing massive hard drive memory swapping. Upgrading RAM reduced processing from 20-25min to 4-5min since it all fit in memory.
I'm running a 16GB company issued laptop. Will more RAM help with speed to locally process data on the laptop?
With working and running Excel locally from home, and processing individual stored files on some far-away (non-database) server any small incremental gain in processing time will help.
- edhans5 years agoCommunity Champion
You'd have to launch Task Manager and watch the mashup engine's memory consumption. If Excel plus the Microsoft.Mashup engine are taking 14GB or more combined, then I'd say more would help as Windows is proably limititing RAM to that and using the hard drive as swap space.
Note that depending on your queries, you may have multiple mashup engines running.
Unfortunately in Excel, you cannot disable parallel processing like you can in Power BI desktop. In a memory constrained environment, having 4 queries run taking up 4GB each will cause your computer to run slower than running them one at a time as the system will have to use swap space to handle the 16GB needed by Excel plus the 2GB or so that Windows needs just to exist.