Forum Discussion
Creating Index within Index
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
That is old, but not sure how old. The better version to use is this:
2008 refers to 2020, August. You might have 1909, which would be Sept 2019, or whatever. I don't know how read the build numbers and convert to when published. But the Version is easy.
Did it work in Power BI Desktop?
I see the problem:
#"Sorted Rows" = Table.Buffer(Table.Sort(myPartition,{{"Delay", Order.Ascending}})), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type), <---- #"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Coarse", each Number.IntegerDivide([Index], 10), Int64.Type), #"Inserted Modulo" = Table.AddColumn(#"Inserted Integer-Division", "Fine", each Number.Mod([Index], 10), type number)Get rid of the ", Int64.Type" part of the index. That is relatively new, and causing your query to crash. So do this:
#"Sorted Rows" = Table.Buffer(Table.Sort(myPartition,{{"Delay", Order.Ascending}})), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1), #"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Coarse", each Number.IntegerDivide([Index], 10), Int64.Type), #"Inserted Modulo" = Table.AddColumn(#"Inserted Integer-Division", "Fine", each Number.Mod([Index], 10), type number)Making indexes an integer is best practice, and Power BI and Excel were updated to do this automatically with this 5th parameter. It causes old versions to crash.
It doesn't matter in this case though because you need to set all data types at the end of Imke's code anyway, so make them integer/whole numbers there.With everyone's help, I think your problem has been solved, so if any of the solutions meet your expectations, please mark them as solutions.
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.
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.
The code did work in Power BI
More information about the Excel version I run.
Unfortunately I have no control of the version I can run at work. And Power BI is not an option with they way we display the data.
Thanks for the outstanding support. Apparently, the solution to the problem was only perplexing to me.
Im a hack at M code. I'm successful enough to keep me in the M code world, but still require immense help as I use
M code only once in awhile because its built into Excel. I'm not immersed in M code, but bang on it to get what I need. The solution is quite elegant and I'm still trying to understand it. Every time I come to this communiny I learn enough to solidy my position for M code as a viable tool to assist my group.
We use PowerQuery to develop an Excel visualization tool. For a single
visualization, the output is up to 15M lines long. For mutliple visualization it could run into the hundreds for millions of lines long.
The only drawback, I'm finding with the Excel Power Query solution is that it requires an Idle Excel while it processes the data.
I was hoping there was a way to tier the accepted solutions. There are two accepted solution:
ziying35: The Implemented solution. It's been implemented into the visualization tool.
ImkeF: A working solution.
Again, THANK YOU.
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.
