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
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"
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"
Here's a link to the file containing the data I showed in my example.
https://drive.google.com/file/d/15bMAH979oPxViF9yH8W-ho1XsBPx8Yoz/view?usp=sharing
Hope you can help. I'm stumped.
-Ron
Can you explain the 3 index columns? Why is the first one 0,0,0, and the 2nd one 1,0,1, and the 9th one 9,0,9, and the 10th one 10,1,0?
I don't see any logic to use.Good question.
In short, the columns add fine tune controls to a time delay mechanism.
Column Index1 simply is an index colum to add an absolute delay control per "Ref". We can then work other calculations based on the values in that column. (Column Index1 is nice to have. It's well documented on how to add this index column per table.)
Columns Index2 and Index3 work together. (These two columns are valuable and are Must Have columns.)
Index2 is a Coarse (time) delay knob.
Index3 is a Fine (time) Step delay knob.
The 3 columns allow us to see a delay parameter against the data we visualize. Moreso with columns Index2 and Index3.
Maybe something to anchor off is Index3. There are 10 Fine Steps per each Coarse delay.
(With the caveat that the quantity of steps may change per coarse. The Fine Step start value may change as well. Our default is 10 Fine Steps per coarse with the steps starting at 0 and ending at 9. Sometimes we use 6 Fine Steps per coarse with the fine steps starting 3 and ending 8. 12 Fine Steps per coarse with the fine steps starting at 4 and ending at 15. )
