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
edhans
5 years agoCommunity Champion
I see the problem:
#"Sorted Rows" = Table.Buffer(Table.Sort(myPartition,{{"Delay", Order.Ascending}})),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type), <----
#"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Coarse", each Number.IntegerDivide([Index], 10), Int64.Type),
#"Inserted Modulo" = Table.AddColumn(#"Inserted Integer-Division", "Fine", each Number.Mod([Index], 10), type number)
Get rid of the ", Int64.Type" part of the index. That is relatively new, and causing your query to crash. So do this:
#"Sorted Rows" = Table.Buffer(Table.Sort(myPartition,{{"Delay", Order.Ascending}})),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
#"Inserted Integer-Division" = Table.AddColumn(#"Added Index", "Coarse", each Number.IntegerDivide([Index], 10), Int64.Type),
#"Inserted Modulo" = Table.AddColumn(#"Inserted Integer-Division", "Fine", each Number.Mod([Index], 10), type number)
Making indexes an integer is best practice, and Power BI and Excel were updated to do this automatically with this 5th parameter. It causes old versions to crash.
It doesn't matter in this case though because you need to set all data types at the end of Imke's code anyway, so make them integer/whole numbers there.