Add rows to table that repeats unique column countdown starting from value in another column, for a set number of times.

[Syndicate_Admin]

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.

[AlienSx]

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

[nthompson08]

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])
        )
        ,

[AlienSx]

please give an example of toollife and currentlife values combination that gives wrong result (or error).  

[Syndicate_Admin]

I got this to work now by adjusting the custom function like so:

fx_tools_add_positions = (r as record, parts as number) as record => 
        if r[Currentlife]=r[Toollife]
        then
        Record.AddField(
            r,
            "positions",
            List.InsertRange(
                 List.Numbers(
                r[Currentlife]+1, 
                Number.RoundAwayFromZero(parts / r[Toollife]), 
                r[Toollife]),0,
                List.Numbers(1,1)
            )
        )
        else
        Record.AddField(
            r,
            "positions",
            List.Numbers(
                r[Currentlife]+1, 
                Number.RoundAwayFromZero(parts / r[Toollife]), 
                r[Toollife])
        )
        ,

[nthompson08]

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.

[AlienSx]

need to change initial value in that function

    fx_tools_add_positions = (r as record, parts as number) as record => 
        Record.AddField(
            r,
            "positions",
            List.Numbers(
                if r[Toollife] = r[Currentlife] then 1 else r[Currentlife] + 1,
                Number.RoundAwayFromZero(parts / r[Toollife]), 
                r[Toollife])
        ),

[wdx223_Daniel]

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

[nthompson08]

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]

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 

[nthompson08]

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]

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