Forum Discussion
Creating Index within Index
- 5 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
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.
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"
- edhans5 years agoCommunity Champion
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- roncruiser5 years agoPost Patron
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
- Anonymous5 years agoNot applicable
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"
- roncruiser5 years agoPost Patron
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
- edhans5 years agoCommunity Champion
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.