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 built in power query->
| Toolname | Toollife | Currentlife | Cost |
| Alpha | 5 | 5 | 50 |
| Bravo | 5 | 2 | 20 |
| Charlie | 3 | 3 | 30 |
| Delta | 6 | 6 | 30 |
and need an output table like this->
| part count | Alpha | Bravo | Charlie | Delta | total changes | total cost |
| 1 | 1 | 0 | 1 | 1 | 3 | 110 |
| 2 | 0 | 0 | 0 | 0 | 0 | 0 |
| 3 | 0 | 1 | 0 | 0 | 1 | 20 |
| 4 | 0 | 0 | 1 | 0 | 1 | 30 |
| 5 | 0 | 0 | 0 | 0 | 0 | 0 |
| 6 | 1 | 0 | 0 | 0 | 1 | 50 |
| 7 | 0 | 0 | 1 | 1 | 2 | 60 |
| 8 | 0 | 1 | 0 | 0 | 1 | 20 |
| 9 | 0 | 0 | 0 | 0 | 0 | 0 |
| 10 | 0 | 0 | 1 | 0 | 1 | 30 |
Where the 1 values under the name columns are the cells in this table where cellvalue=Toollife of [columnname]. The countdown under each [Toolname] column should start from the [Currentlife] value for the same Toolname set in the original table. With [part count] counting up from 1 to #"Default_Values"{0}[part_count] (this is another table with some default values built into it).
| part count | Alpha | Bravo | Charlie | Delta |
| 1 | 5 | 2 | 3 | 6 |
| 2 | 4 | 1 | 2 | 5 |
| 3 | 3 | 5 | 1 | 4 |
| 4 | 2 | 4 | 3 | 3 |
| 5 | 1 | 3 | 2 | 2 |
| 6 | 5 | 2 | 1 | 1 |
| 7 | 4 | 1 | 3 | 6 |
| 8 | 3 | 5 | 2 | 5 |
| 9 | 2 | 4 | 1 | 4 |
| 10 | 1 | 3 | 3 | 3 |
TIA.
11 Replies
- wdx223_DanielCommunity Champion
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- AnonymousNot applicable
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.
- slorinSuper User
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
- AlienSxSuper User
let tools_raw = 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]), tools = Table.TransformColumnTypes(tools_raw,{{"Toollife", Int64.Type}, {"Currentlife", Int64.Type}, {"Cost", Currency.Type}}), // number of parts parameter parts_number = 10, // names and parts lists tool_names = List.Buffer(tools[Toolname]), parts_list = List.Buffer({1..parts_number}), // function calculates positions of tools and adds field with positions list to tool record fx_tools_add_positions = (r as record, parts as number) as record => Record.AddField( r, "positions", List.Numbers( List.Max({r[Toollife] - r[Currentlife], 1}), Number.RoundAwayFromZero(parts / r[Toollife]), r[Toollife]) ), // creates list of tools records from tools table tools_with_positions = List.Buffer( List.Transform( Table.ToRecords(tools), each fx_tools_add_positions(_, parts_number) ) ), // iterates list of parts then list of tools for each part to get rows of final table r = List.Transform( parts_list, (x) => let p = [part_count = x, total_changes = 0, total_cost = 0], w = List.Accumulate( List.Positions(tool_names), p, (s, c) => let t_rec = tools_with_positions{c}, bit = Number.From( List.Contains( Record.Field(t_rec, "positions"), x ) ), name_field = Record.AddField(s, t_rec[Toolname], bit), other_fields = Record.TransformFields( name_field, {{"total_changes", each s[total_changes] + bit}, {"total_cost", each s[total_cost] + bit * t_rec[Cost]}} ) in other_fields ) in w ), // list of records >> table to_table = Table.FromRecords(r), // reordering columns. You may add rename columns step if you want reorder = Table.ReorderColumns(to_table,{"part_count"} & tool_names & {"total_changes", "total_cost"}) in reorder- AnonymousNot applicable
This almost worked. The math breaks down with larger tool life and current life values. I adjusted the function and it looks like the values are populating correctly, EXCEPT the [part_count]=1 field is not populating with a 1 where toollife=currentlife. I cannot figure out how to adjust it for that yet.
fx_tools_add_positions = (r as record, parts as number) as record => Record.AddField( r, "positions", List.Numbers( r[Currentlife]+1, Number.RoundAwayFromZero(parts / r[Toollife]), r[Toollife]) ) ,- AlienSxSuper User
please give an example of toollife and currentlife values combination that gives wrong result (or error).