Forum Discussion

roncruiser's avatar
roncruiser
Icon for Post Patron rankPost 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 Group By table I need the following two index columns.

 

Example>

Index1, Index2

0,0

0,1

0,2

0,3

0,4

1,0

1,1

1,2

1,3

1,4

2,0

2,1

2,2

2,3

2,4

 

Note: The first index columns actually has 10 repeats but I used 5 to shorten this example.

Thanks... 

  • 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

     

40 Replies

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      6 years ago
      ImkeF - Holy smokes! Simultaneous!! I knew this would be up your alley! 🙂
    • roncruiser's avatar
      roncruiser
      Icon for Post Patron rankPost Patron
      6 years ago

      ImkeF 

       

      Thanks for the Quick respose but not quite what I was looking for. Please correct me.  The solution you provided adds an index column to each Grouped by table.  I've already had that. 

       

      I need an addtional two columns per GroupBy table.  For example.

       

      Within each already created GroupBy table:

       

      Index1(have),Index2(need),Index3(need)

      0,0,0

      1,0,1

      2,0,2

      3,0,3

      4,0,4

      5,1,0

      6,1,1

      7,1,2

      8,1,3

      9,1,4

      10,2,0

      11,2,1

      12,2,2

      13,2,3

      14,2,4

       

      Hope that clarifies better, maybe you did give the solution and I don't see it.

       

      • ImkeF's avatar
        ImkeF
        Icon for Community Champion rankCommunity Champion
        6 years ago

        Hi roncruiser ,

        it's hard for me what you're after here.

        But the figures you've provided can be achieved by the following code:

         

        let
    Source = {0..14},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Inserted Integer-Division" = Table.AddColumn(#"Converted to Table", "Integer-Division", each Number.IntegerDivide([Column1], 5), Int64.Type),
    #"Inserted Modulo" = Table.AddColumn(#"Inserted Integer-Division", "Modulo", each Number.Mod([Column1], 5), type number)
in
    #"Inserted Modulo"

         

  • ziying35's avatar
    ziying35
    Icon for Impactful Individual rankImpactful 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
      Icon for Post Patron rankPost 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.