Forum Discussion
Add rows to table that repeats unique column countdown starting from value in another column, for a set number of times.
Having trouble figuring out the direction I need to go for this table I want to make. Will continue to research functions but wanted to pose the general question to the community. I have this table...
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcswpyEhU0lEyhWEDpVidaCWnosSyfKiQEQhDhJ0zEotyMlOBAsYwDJFwSc0pARljBsUg4VgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Toolname = _t, Toollife = _t, Currentlife = _t, Cost = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Toollife", Int64.Type}, {"Currentlife", Int64.Type}, {"Cost", Int64.Type}}),
Custom1 = let PartCount=10 in #table({"Part Count"}&#"Changed Type"[Toolname]&{"Total Changes","Total Cost"},List.Transform({1..PartCount},(x)=>let a=List.Transform(Table.ToRows(#"Changed Type"),each let n=Byte.From(Number.Mod(_{1}-x+1+_{2},_{1})=0) in {n,n*_{3}}),b=List.Zip(a) in {x}&b{0}&{List.Sum(b{0}),List.Sum(b{1})}))
in
Custom1
This worked but is extremely slow, my table is 40 toolnames long with 250-500 part counts. Is this speed expected with a table that large? Even loading 10 rows probably took 2 minutes.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcswpyEhU0lEyhWEDpVidaCWnosSyfKiQEQhDhJ0zEotyMlOBAsYwDJFwSc0pARljBsUg4VgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Toolname = _t, Toollife = _t, Currentlife = _t, Cost = _t]),
ChangeType = Table.TransformColumnTypes(Source,{{"Toollife", Int64.Type}, {"Currentlife", Int64.Type}, {"Cost", type number}}),
Parts_number = 10,
Pattern = Table.AddColumn(ChangeType, "Pattern", each {1} & List.Repeat({0}, [Toollife]-1)),
ListRepeat = Table.AddColumn(Pattern, "List.Repeat", each List.Repeat([Pattern], Number.IntegerDivide(Parts_number, [Toollife]) +2)),
ListRange = Table.AddColumn(ListRepeat, "ListRange", each List.Range([List.Repeat], [Toollife]-[Currentlife], Parts_number)),
Table = Table.FromColumns({{1..Parts_number}} & ListRange[ListRange], {"part count"} & ListRange[Toolname]),
Quantity = Table.AddColumn(Table, "Quantity", each List.Skip(Record.ToList(_), 1)),
Changes = Table.AddColumn(Quantity, "Total changes", each List.Sum([Quantity])),
Cost = Table.AddColumn(Changes, "Cost", each List.Sum(List.Transform(List.Zip({[Quantity], ChangeType[Cost]}), List.Product))),
RemoveColumns = Table.RemoveColumns(Cost,{"Quantity"})
in
RemoveColumnsTested with 200 toolnames and 500 parts counts. Very fast.
Stéphane
- This is still not fast for me. Could I be setting up the Source wrong? I duplicate my table then go to advanced editor and copy your code, replacing the Source = line to this: Source = #"first_tool_data"
