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
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.
- roncruiser6 years agoPost Patron
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. )
- ImkeF6 years agoCommunity Champion
Hi roncruiser ,
I still don't understand how the data you've provided should actually match together.
Could you please provide sample data that have a reall "before and after"-scenario that allows the business logic to follow up?
- roncruiser6 years agoPost Patron
ImkeF Sure.
Sure. This file contains before and after data. Each on it's own tab.
The "after" sample has been truncated to include just a few Refs
https://drive.google.com/file/d/15Ep2jzlHm-q5qC7bM3e5XDDfaPk07vwC/view?usp=sharing