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

     

ImkeF's avatar
ImkeF
Community Champion
6 years ago

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.

 

roncruiser's avatar
roncruiser
Post Patron
6 years ago

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's avatar
    ziying35
    Impactful Individual
    6 years ago

    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