Forum Discussion
Syndicate_Admin
3 years agoAdministrator
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...
AlienSx
3 years agoSuper 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- Anonymous3 years agoNot 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]) ) ,- AlienSx3 years agoSuper User
please give an example of toollife and currentlife values combination that gives wrong result (or error).
- Anonymous3 years agoNot applicable
When I input 100 for a tool life and 2 for a current life, the first "1" set for that tool was at part #98, then every 100 parts# after that.