Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
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])
)
,
please give an example of toollife and currentlife values combination that gives wrong result (or error).
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])
)
,
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.
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])
),
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
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.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.