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

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?

roncruiser's avatar
roncruiser
Post Patron
6 years ago

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

 

 

Thank you.

 

  • ziying35's avatar
    ziying35
    Impactful Individual
    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