Forum Discussion
Creating Index within Index
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
roncruiser - perhaps you could provide some specific data with expected results that would help us see what you are doing. To me, it seems you can just add an index, then add another sub-index of 0-4 using this code.
let
Source = {0..10},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"First Index"}, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Second Index", each {1..4}),
#"Expanded Second Index" = Table.ExpandListColumn(#"Added Custom", "Second Index")
in
#"Expanded Second Index"
But without data, we are just guessing. See links at bottom for providing actual data.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum
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...
- Inside each SubIndexes Table sorting the values in the "Delay" column in Ascending order. Then...
- 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
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"