Creating Index within Index

Hi roncruiser ,

please check out this code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddtBbtxGEAXQu3idAKyuZje5laO5hJH7XyMyWyNrWC8LwwnxIwR5tkf/p/jr14/tx18/+naObfv9V18/Pv/290///vXrR5TU34FYq7GGWNZYItZrrCO219iO2KixgdissYnYUWMHYmeNnfrPWxX+DjoIQhIBipBFACOkEeAIeQRAQiIBkpBJACWkEmAJuQRgQjINMk0yDTKNv0f0m0QyDTJNMg0yTTINMk0yDTJNMg0yTTINMk0yDTJNMgmZlExCJiWTkEn++aU/wCSTkEnJJGRSMgmZlExCJiWTkEnJJGRSMh0yXTIdMl0yHTJdMh0ynZ8t+nCRTIdMl0yHTJdMh0yXTIdMl0yHTJfMDpldMjtkdsnskNkls0Nml8wOmZ2f+/rgl8wOmV0yO2R2yeyQ2SWzQ2aXzIDMkMyAzJDMgMyQzIDMkMyAzJDMgMzg92T6pkwyAzJDMgMyQzIDMkMyEzJTMhMyUzITMlMyEzJTMhMyUzITMlMyEzKT3y/rG2bJTMhMyUzITMkckLmebXELQuZ6VoKQuZ4d9yBkrmdv5y0ImevZ+XYLQuZ69va4BSFzPXt7O16DkLmePR63oMrMsYLxEoTM9ewjuH8PnpC5npUgZK5nJQiZ61kJQuZ69hH850l+BSFzPXsGz2cQMtezx+N8DULmevbxFc+XIGTO+Sf49hWEzHn8+Xd8PJ5BFc0TwfXruFTNjdGKw6q5VRs2za3SsGhuVYY9c6swrJlbdWHL3CoLS+ZWVdgxt4rC8o/2/z/lHyAUQft3+Uf7d/lH+3f5R/t3+Uf7d/lH+3f5R/t3+Uf7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7fy3//RmsMq/l/ytYZV7L/1ewyryW//EM4lvmF5mPXyIrWGVey//H11/BKvNa/p9BtP/ju0yf75/BKnN8l+mPZ7DKHN9k+ng8PoNV5vgu8/MrWGWObzL9/StYZVat7f0WrDLXo+3n9QE2/wSrzPXo4yvegmgzx5+v+PgTrDKr1q6v+Hg8g2j/16P3n/v7y1dE+1/l/+d8f/2KVWaV/xKsMqv8v8/Ha7DKrPJfglVmlf8SrDKr/JdglVnlvwSrzGf5f9yCKJongm2rMs/uf0+iam7BJLrm1phE2dySSbTNrTOJurntTKJvboNJFM5tMonGuYmoYQtY+0BJag0IGnEOoJH2gKCRBoGgkRaBoJEmgaCRNoGgkUaBoJFWgaCRZoGgEXaBtRXUJIwajbAMrLWgJmHUaIRtYO0FNQmjRiOsA2sxqEkYNRphH1ibQU3CqNEIC8FaDWoSRkkjbARrN6hJDWs0wkqwloOahFHSCDvB2g5qEkZJIywFaz2oSRgljbAVrP2gJmHUaYS1YC0INQmjTiPsBdFphMFgjQg1CaNOI0wGa0aoSRh1GmE0WENCSWI1WEtCTcJopxF2g7Ul1CSMdhphOVhrQk1qoqYRtoO1J9QkjHYaYT1Yi0JNwminEfaDtSnUJIwGjbAgrFWhJmE0aIQNYe0KNQmjQSOsCGtZqEkYDRphR1jbQk3CaNAIS8JaF2oSRpNG2BLWvlCTMJo0wpqwFoaahNGkEfaEtTHUpP5nD42wKKyVoSZhNGmETWHtDDUJo4NGWBXW0lCTMDpohF1hbQ01CaODRlgW1tpQkzA6aIRtIQ4aYVyIg0ZYF+KkEeaFOGmEfSFOGmFgiJNGWBjipBEmhjhphI0hThphZIiTRlgZgjNDw8wQ3BkSO0PjzpDYGRp3htSVAXeGxM7QuDMkdobGnSGxMzTuDImdoXFnSOwMjTtDYmdo3BkSO0PjzpDYGRp3hsTO0LgzJHaGxp0hsTM07gyJnaFxZ0jsDI07Q2JnaNwZEjtD486Q2Bkad4bEztC4M6TuD7gzpA4QuDMkLxBopBME7gypGwTuDKkjBO4MqSsE7gypMwTuDKk7BO4MqUME7gyJnaFxZ0jsDI07Q2JnaNwZEjtD486Q2Bkad4bEztC4MyR2hsadIbEzNO4MiZ2hcWdI7AyNO0NiZ2jcGRI7Q+POkNgZGneGvO0M68253z8+/8nfP61gr0G+Z5e3mWEl+arKbWZYSb+rMpHkyyq3mWEl+bbKbWZYSb6ucpsZVpLvq9xmhpXkCyu3mWEl+S7RDiK/gZc7kai0S4mHeLmLiad4ucuJx3i5C4rneLlLigd5OUTFk7wcsuJRXg5h8Swvh7R4mJdDWjzNy8HfU9Qa0uJ5Xg5p8UAvh7R4opdDWjzSyyktnunllBYP9XJKy+/pTWn5Tb0pLb+rN6Xlt/Um/wik1pSW39ib0vI7e1NafmvvkNb/vLcnLb+5d0iL53t5SIsHfHlIiyd8eUiLR3x5SItnfHnwE4tah7R4ypeHtHjMl6e0eM6Xp7R40JentHjSl6e0eNSXp7R41pentHjYl6e0eNqXp7R43Jcnv8Gg1iktHvj1TVo88eubtHjk1zdp8cyvb9LioV/fpMVTv75Ji8d+fZMWz/36Ji0e/PVNWjz56xu/H6RWSItnfz2kxcO/HtLi6V8PafH4r4e0eP7XQ1o8AOwhLb7/10NafAOwh7T4DmAPafEtwN6kxfcAe5MW3wTsTVp8F7A3aR3UatLi+4C9SYtvBPYmrevTtEaldX2aHm/3qLTWe4ElKi2+GdhTWten6Uc0XqNsW3o7sKe0Pkf8c3+NSutzxb9HpfU543+L/vsf", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Iteration = _t, Frequency = _t, Ref = _t, Delay = _t, Capture1 = _t, Capture2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Iteration", Int64.Type}, {"Frequency", Int64.Type}, {"Ref", Int64.Type}, {"Delay", Int64.Type}, {"Capture1", type text}, {"Capture2", type text}}),
    fnTransformation = (myPartition as table) => let
            #"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)
        in
            #"Inserted Modulo",
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Ref"}, {{"All", each _, type table [Iteration=nullable number, Frequency=nullable number, Ref=nullable number, Delay=nullable number, Capture1=nullable text, Capture2=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "ApplyFunction", each fnTransformation([All])),
    #"Expanded ApplyFunction" = Table.ExpandTableColumn(#"Added Custom", "ApplyFunction", {"Iteration", "Frequency", "Delay", "Capture1", "Capture2", "Index", "Coarse", "Fine"}, {"Iteration", "Frequency", "Delay", "Capture1", "Capture2", "Index", "Coarse", "Fine"})
in
    #"Expanded ApplyFunction"

I've used the techniques from my previous post:

The "Coarse"-column is created using the IntegerDivide-Function and the "Fine"-column using the Modulo.
They are hardcoded to 10, but this could be turned into a variable once you're able to specify how you're going to "feed" the variing numbers into this solution.

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.

thanks edhans 

for stepping in here and clearing things up!

roncruiser 

try this:

// Before
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    chType = Table.TransformColumnTypes(Source,{{"Delay", type number}}),
    fnRec = (tbl)=> List.Transform({0..Table.RowCount(tbl)}, each [Index1=_, Coarse=Number.IntegerDivide(_, 10), Fine=Number.Mod(_, 10)]),
    fx = Function.ScalarVector(
            type function(rec as text) as text,
            (tbl)=>
                 let t=Table.Buffer(tbl) in fnRec(t)
          ),
    group = Table.Group(chType, {"Ref"}, {"SubIndexes", each Table.AddColumn( Table.Sort(_, {"Delay", 0}), "rec", (r)=>fx(r))})[SubIndexes],
    cmbTbls = Table.Combine(group),
    expd = Table.ExpandRecordColumn(cmbTbls, "rec", {"Index1", "Coarse", "Fine"})
in
    expd

roncruiser 

Or try this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    chType = Table.TransformColumnTypes(Source,{{"Delay", type number}}),
    fx = (tbl)=>
       let
         sortedTbl = Table.Sort(tbl, {"Delay", 0}),
         rows = List.Buffer(Table.ToRows(sortedTbl)),
         n = List.Count(rows),
         gen = List.Generate(
                   ()=>{0,{}}, 
                   each _{0}<=n, 
                   each {
                           _{0}+1, 
                           let idx_lst={_{0}, Number.IntegerDivide(_{0},10), Number.Mod(_{0}, 10)} 
                           in List.InsertRange(rows{_{0}}, 4, idx_lst)
                        },
                   each _{1}
         ),
        toTbl = Table.FromRows(List.Skip(gen), List.InsertRange(Table.ColumnNames(sortedTbl), 4, {"Index1", "Coarse", "Fine"}))
      in
        toTbl,
    group = Table.Group(chType, "Ref", {"t", fx})[t],
    result = Table.Combine(group)
in
    result

I've re-provided two solutions, both of which are similar in terms of code efficiency, and the last one has the table fields sorted in the order you expect them to be.

If my code solves your problem, mark it as a solution

Hi roncruiser ,

the error must come from an earlier step or some changes you've made to the function code.
Can you share the full M-code that you've used please?

roncruiser 

I'm working on the code. I'll repost it  when I'm done

roncruiser 

// sample
let
    Source = {0..100},
    n = List.Count(Source),
    gen = List.Generate(
               ()=>{0, {}},
               each _{0}<=n, 
               each let Coarse=Number.IntegerDivide(_{0}, 10)+3
                    in  if _{1}{3}? =null or _{1}{3}=9 then {_{0}+1, {0, _{0}, Coarse , 3}}
                    else {_{0}+1, {_{1}{0}+1, _{0}, Coarse, _{1}{3}+1}},
               each List.Skip(_{1})
          ),
    toTbl = Table.FromRows(List.Skip(gen), {"Index1", "Coarse", "Fine"})
in
    toTbl

You see if I understand what you're saying, and if that's what you want, then I'll go ahead and write the whole thing.

What I'm trying to determine is how many times I need to repeat the same Coarse value, now I'm writing the code to repeat it 10 times, if the number of times the Coarse value needs to be repeated is variable, then under what conditions does it change?

That is really odd roncruiser - I used Imke's code in both Power BI (August 2020) and Excel from Office 365 (version 2008) and it works fine. I cannot see any new things in her code that an older version might break. What is your Excel version. "Pro Plus" doesn't tell a version, but a licensing level. Is this Excel 2010, 2013, 2016, 2019, or Excel from office 365, which should, at most, be 6 months old if on a deferred channel.

Also, paste this code in Power BI and see if it works there just to make sure you are doing that part correctly.

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?

roncruiser 

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.

1. Running Excel 64 bit
2. 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.

ImkeF Sure.

Sure.  This file contains before and after data.  Each on it's own tab.

The "after" sample has been truncated to include just a few Refs

https://drive.google.com/file/d/15Ep2jzlHm-q5qC7bM3e5XDDfaPk07vwC/view?usp=sharing

This is tricky and I keep running into dead ends.

I'm hoping I can end up with a solution.  So I came here to Power BI community

for fresh new perspective from some of the experts.

• The trick with the Delay column is that it must be sorted Ascending for each Ref column value.
• Then the Index columns are added. With flexibility to change Coarse start value, and to change the Fine range values.
  • Right now the Coarse start value is 0, and the Fine range is 0-9.

Thanks so much...

ImkeF 

Almost there.  Thank you very much ImkeF !

I get the following error.  I understand what you are doing with the code, but can't get around this error.

ziying35   Sure.   This looks good. 

• So, default settings for the Coarse start value is 0, and the Fine range is 0-9.
•  Is it possible add functionality to set the Coarse Value to start at n start value and adjust the Fine range from n to +n?
  • For example: Coarse start value at 3.  With Fine range from 3-9 steps.

70% of the time we use default settings, but some times we vary the Coarse and Fine range parameters.

Thanks again for the work... I have to settle down and digest your code....

Adding control for Coarse Start Value and the Fine Step Range allows control of the time delay sensitivity for each Ref step. This allows finer control of the delay which ultimately allows a wider optimization range. 

ImkeF  Sure.  Thanks. The code I used is basically a copy and paste of your code direct from the web page.  Pasted directly into a blank query.

Maybe something changes?  I'm running the latest version of the Exel office "MS Office Pro Plus".

I tried to substite the real source data but I get the same error.  The error always occurrs at row "Added Custom".  Up until that particular line, all lines show properly.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddtBbtxGEAXQu3idAKyuZje5laO5hJH7XyMyWyNrWC8LwwnxIwR5tkf/p/jr14/tx18/+naObfv9V18/Pv/290///vXrR5TU34FYq7GGWNZYItZrrCO219iO2KixgdissYnYUWMHYmeNnfrPWxX+DjoIQhIBipBFACOkEeAIeQRAQiIBkpBJACWkEmAJuQRgQjINMk0yDTKNv0f0m0QyDTJNMg0yTTINMk0yDTJNMg0yTTINMk0yDTJNMgmZlExCJiWTkEn++aU/wCSTkEnJJGRSMgmZlExCJiWTkEnJJGRSMh0yXTIdMl0yHTJdMh0ynZ8t+nCRTIdMl0yHTJdMh0yXTIdMl0yHTJfMDpldMjtkdsnskNkls0Nml8wOmZ2f+/rgl8wOmV0yO2R2yeyQ2SWzQ2aXzIDMkMyAzJDMgMyQzIDMkMyAzJDMgMzg92T6pkwyAzJDMgMyQzIDMkMyEzJTMhMyUzITMlMyEzJTMhMyUzITMlMyEzKT3y/rG2bJTMhMyUzITMkckLmebXELQuZ6VoKQuZ4d9yBkrmdv5y0ImevZ+XYLQuZ69va4BSFzPXt7O16DkLmePR63oMrMsYLxEoTM9ewjuH8PnpC5npUgZK5nJQiZ61kJQuZ69hH850l+BSFzPXsGz2cQMtezx+N8DULmevbxFc+XIGTO+Sf49hWEzHn8+Xd8PJ5BFc0TwfXruFTNjdGKw6q5VRs2za3SsGhuVYY9c6swrJlbdWHL3CoLS+ZWVdgxt4rC8o/2/z/lHyAUQft3+Uf7d/lH+3f5R/t3+Uf7d/lH+3f5R/t3+Uf7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7Z/kPtH+W/0D7fy3//RmsMq/l/ytYZV7L/1ewyryW//EM4lvmF5mPXyIrWGVey//H11/BKvNa/p9BtP/ju0yf75/BKnN8l+mPZ7DKHN9k+ng8PoNV5vgu8/MrWGWObzL9/StYZVat7f0WrDLXo+3n9QE2/wSrzPXo4yvegmgzx5+v+PgTrDKr1q6v+Hg8g2j/16P3n/v7y1dE+1/l/+d8f/2KVWaV/xKsMqv8v8/Ha7DKrPJfglVmlf8SrDKr/JdglVnlvwSrzGf5f9yCKJongm2rMs/uf0+iam7BJLrm1phE2dySSbTNrTOJurntTKJvboNJFM5tMonGuYmoYQtY+0BJag0IGnEOoJH2gKCRBoGgkRaBoJEmgaCRNoGgkUaBoJFWgaCRZoGgEXaBtRXUJIwajbAMrLWgJmHUaIRtYO0FNQmjRiOsA2sxqEkYNRphH1ibQU3CqNEIC8FaDWoSRkkjbARrN6hJDWs0wkqwloOahFHSCDvB2g5qEkZJIywFaz2oSRgljbAVrP2gJmHUaYS1YC0INQmjTiPsBdFphMFgjQg1CaNOI0wGa0aoSRh1GmE0WENCSWI1WEtCTcJopxF2g7Ul1CSMdhphOVhrQk1qoqYRtoO1J9QkjHYaYT1Yi0JNwminEfaDtSnUJIwGjbAgrFWhJmE0aIQNYe0KNQmjQSOsCGtZqEkYDRphR1jbQk3CaNAIS8JaF2oSRpNG2BLWvlCTMJo0wpqwFoaahNGkEfaEtTHUpP5nD42wKKyVoSZhNGmETWHtDDUJo4NGWBXW0lCTMDpohF1hbQ01CaODRlgW1tpQkzA6aIRtIQ4aYVyIg0ZYF+KkEeaFOGmEfSFOGmFgiJNGWBjipBEmhjhphI0hThphZIiTRlgZgjNDw8wQ3BkSO0PjzpDYGRp3htSVAXeGxM7QuDMkdobGnSGxMzTuDImdoXFnSOwMjTtDYmdo3BkSO0PjzpDYGRp3hsTO0LgzJHaGxp0hsTM07gyJnaFxZ0jsDI07Q2JnaNwZEjtD486Q2Bkad4bEztC4M6TuD7gzpA4QuDMkLxBopBME7gypGwTuDKkjBO4MqSsE7gypMwTuDKk7BO4MqUME7gyJnaFxZ0jsDI07Q2JnaNwZEjtD486Q2Bkad4bEztC4MyR2hsadIbEzNO4MiZ2hcWdI7AyNO0NiZ2jcGRI7Q+POkNgZGneGvO0M68253z8+/8nfP61gr0G+Z5e3mWEl+arKbWZYSb+rMpHkyyq3mWEl+bbKbWZYSb6ucpsZVpLvq9xmhpXkCyu3mWEl+S7RDiK/gZc7kai0S4mHeLmLiad4ucuJx3i5C4rneLlLigd5OUTFk7wcsuJRXg5h8Swvh7R4mJdDWjzNy8HfU9Qa0uJ5Xg5p8UAvh7R4opdDWjzSyyktnunllBYP9XJKy+/pTWn5Tb0pLb+rN6Xlt/Um/wik1pSW39ib0vI7e1NafmvvkNb/vLcnLb+5d0iL53t5SIsHfHlIiyd8eUiLR3x5SItnfHnwE4tah7R4ypeHtHjMl6e0eM6Xp7R40JentHjSl6e0eNSXp7R41pentHjYl6e0eNqXp7R43Jcnv8Gg1iktHvj1TVo88eubtHjk1zdp8cyvb9LioV/fpMVTv75Ji8d+fZMWz/36Ji0e/PVNWjz56xu/H6RWSItnfz2kxcO/HtLi6V8PafH4r4e0eP7XQ1o8AOwhLb7/10NafAOwh7T4DmAPafEtwN6kxfcAe5MW3wTsTVp8F7A3aR3UatLi+4C9SYtvBPYmrevTtEaldX2aHm/3qLTWe4ElKi2+GdhTWten6Uc0XqNsW3o7sKe0Pkf8c3+NSutzxb9HpfU543+L/vsf", BinaryEncoding.Base64), Compression.Deflate)),

let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Iteration = _t, Frequency = _t, Ref = _t, Delay = _t, Capture1 = _t, Capture2 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Iteration", Int64.Type}, {"Frequency", Int64.Type}, {"Ref", Int64.Type}, {"Delay", Int64.Type}, {"Capture1", type text}, {"Capture2", type text}}),
fnTransformation = (myPartition as table) => let
#"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)
in
#"Inserted Modulo",
#"Grouped Rows" = Table.Group(#"Changed Type", {"Ref"}, {{"All", each _, type table [Iteration=nullable number, Frequency=nullable number, Ref=nullable number, Delay=nullable number, Capture1=nullable text, Capture2=nullable text]}}),
#"Added Custom" = Table.AddColumn(#"Grouped Rows", "ApplyFunction", each fnTransformation([All])),  // Error occurrs here
#"Expanded ApplyFunction" = Table.ExpandTableColumn(#"Added Custom", "ApplyFunction", {"Iteration", "Frequency", "Delay", "Capture1", "Capture2", "Index", "Coarse", "Fine"}, {"Iteration", "Frequency", "Delay", "Capture1", "Capture2", "Index", "Coarse", "Fine"})
in
#"Expanded ApplyFunction"

Thanks for the responsiveness ImkeF 

edhans ImkeF 

It is odd. 

ziying35 Thank you.   Here are a few examples I created.

Thank you.

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.

edhans Thank you.  Good catch.

I'll try it out.

ziying35 ImkeF edhans 

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.