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 built in power query->

ToolnameToollifeCurrentlifeCost
Alpha55

50

Bravo5220
Charlie3330
Delta6630

 

and need an output table like this->

part countAlphaBravoCharlieDeltatotal changestotal cost
110113110
2000000
30100120
40010130
5000000
61000150
70011260
80100120
9000000
100010130

 

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 countAlphaBravoCharlieDelta
15236
24125
33514
42433
51322
65211
74136
83525
92414
101333

 

TIA.

 

11 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Community 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

     

    • Anonymous's avatar
      Anonymous
      Not 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.

      • slorin's avatar
        slorin
        Super 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
        RemoveColumns

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

        Stéphane 

  • 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
    • Anonymous's avatar
      Anonymous
      Not 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])
              )
              ,

       

      • AlienSx's avatar
        AlienSx
        Super User

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