Forum Discussion

roncruiser's avatar
roncruiser
Post Patron
6 years ago
Solved

Creating Index within Index

Hi, I'm struggling to create a redundant index column and another index column which references the redundant index column.   I've grouped a set of data using the Group By function and within each ...
  • ziying35's avatar
    ziying35
    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

     

ziying35's avatar
ziying35
Impactful Individual
6 years ago

Hi, roncruiser 

Change the code for the query called Example in the sample file you uploaded to the Google Drive to the following:

 

// Example
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    fnRec = (tbl)=> List.Transform({0..Table.RowCount(tbl)}, each [Index1=_, Index2=Number.IntegerDivide(_, 10), Index3=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(Source, {"Ref"}, {"SubIndexes", each Table.AddColumn(_, "rec", (r)=>fx(r))})[SubIndexes],
    cmbTbls = Table.Combine(group),
    expd = Table.ExpandRecordColumn(cmbTbls, "rec", {"Index1", "Index2", "Index3"})
in
    expd

 

 

The result of the code run is shown below:

roncruiser's avatar
roncruiser
Post Patron
6 years ago

ziying35 

 

Almost!  The trick is getting the Delay column to Sort Ascending per each Ref.  Then add the index columns.

 

  1. Sort Delay column Ascending per each Ref.  The lowest Delay values per each Ref is the 0 starting point for each added index column.
  2. Then add index columns.  The trick here is the Coarse starting value may change, and the Fine range may change.  This is tricky.  I'm working on it but I keep running into dead ends.  

I've added a before and after example in a previous post.