Forum Discussion
roncruiser
5 years agoPost Patron
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 ...
- 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
roncruiser
5 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
edhans
5 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.