Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Administrator
3 years ago

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...
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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.

slorin's avatar
slorin
Super User
3 years ago
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
RemoveColumns

Tested with 200 toolnames and 500 parts counts. Very fast.

Stéphane 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago
    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"
    • slorin's avatar
      slorin
      Super User
      3 years ago

      may be add "List.Buffer"

       

      let
      Source = Your_Source,
      ChangeType = Table.TransformColumnTypes(Source,{{"Toollife", Int64.Type}, {"Currentlife", Int64.Type}, {"Cost", type number}}),
      Parts_number = 500,
      ListBuffer = List.Buffer(ChangeType[Cost]),
      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],ListBuffer}),List.Product))),
      RemoveColumns = Table.RemoveColumns(Cost,{"Quantity"})
      in
      RemoveColumns

       

      3 seconds with 200 toolnames and Parts_number = 500

       

      Stéphane