Creating Index within Index

[roncruiser]

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... 

[edhans]

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.

[roncruiser]

@edhans @ImkeF 

It is odd. 

[edhans]

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]

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.

[ziying35]

@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.

[roncruiser]

@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.

[edhans]

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.

[roncruiser]

@edhans 

I'm running a 16GB company issued laptop.  Will more RAM help with speed to locally process data on the laptop?

With working and running Excel locally from home, and processing individual stored files on some far-away (non-database) server any small incremental gain in processing time will help.

[edhans]

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.

[edhans]

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.

[ImkeF]

thanks @edhans 

for stepping in here and clearing things up!

[roncruiser]

@edhans Thank you.  Good catch.

I'll try it out.

[roncruiser]

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]

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]

@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]

@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

[roncruiser]

@edhans  Sure.  Thank you

Let me see if I can explain with the following...

This is my source data:

I then transform the data using the Group By feature:

After Group By:

Each Value in the "Ref" column now has it's own table.

Here's what I can't figure out...

1. Inside each SubIndexes Table sorting the values in the "Delay" column in Ascending order. Then...
2. Inside each SubIndexes Table, I need to add the following columns:

Index1,Index2,Index3

0,0,0

1,0,1

2,0,2

3,0,3

4,0,4

5,0,5

6,0,6

7,0,7

8,0,8

9,0,9

10,1,0

11,1,1

12,1,2

13,1,3

14,1,4

15,1,5

16,1,6

17,1,7

18,1,8

19,1,9

.... until the end of the column inside each table.

I'll see if I can upload a sample set of data.

-correction made- 082420 @1754

[Anonymous]

I have seen, even without having read all the messages, that the problem has found several solutions.
I don't intend to add solutions, also because I don't read the specs right.

I just wanted to expose a new (?) point of view on this sequence ..

Index1,Index2,Index3

0,0,0

1,0,1

2,0,2

3,0,3

4,0,4

5,0,5

6,0,6

7,0,7

....

....

let
    nrow=Table.RowCount(Table2),
    Query1 = Table.FromColumns({{0..nrow}},{"L1"}),
    #"Duplicated Column" = Table.AddColumn(Query1,"L2",each Text.PadStart(Text.End(Text.From(_[L1]),2),2,"0")),
    #"Split Column by Position" = Table.SplitColumn(#"Duplicated Column", "L2", Splitter.SplitTextByRepeatedLengths(1), {"L2.1", "L2.2"})
in
    #"Split Column by Position"

[Greg_Deckler]

@ImkeF - Holy smokes! Simultaneous!! I knew this would be up your alley! 🙂

[Greg_Deckler]

@roncruiser - Hmm, not sure of the Power Query solution for this, but maybe @ImkeF or @edhans knows. I did do something similar in DAX called Cthulhu:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/m-p/509739#M211