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.
I have a function that creates a running total. As it currently stands (without the red code) it will create a running sum for each group. I would like to expand on this somehow to include logic that says restart the running sum at the end of the current group or when the current number is greater than the number in the column Max Container. My attempt to do is in in red. What am I missing?
(MyTable as table) as table =>
let
Source = Table.Buffer(MyTable),
TableType = Value.Type(Table.AddColumn(Source, "Running Sum", each null, type number)),
current = List.Skip(List.Accumulate(Source[SKU Cumulative Qty],{0},(current,state) => (current & {List.Last(current) + state}) or (current & {List.Last(Source[Max Container]) + state}) )),
AddedRunningSum = Table.FromColumns(Table.ToColumns(Source)&{current},TableType)
in
AddedRunningSum
Solved! Go to Solution.
hey
and what was the BIN# in your post?
Now this does simplify things 🙂
This will be my last post on this thread 😉
3 beers - some kudoes and 3 solutions as min, allright @Anonymous ? 😉
(tTable as table) as table =>
let
Group = Table.Group(tTable, {"Order"}, {{"AllRows", each _}}),
fnRowIndex = (tbl as table, sumcolumn as text, rowindex as number) =>
let
#"Removed Other Columns" = Table.SelectColumns(tbl,{sumcolumn, "Index"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each [Index] <= rowindex),
#"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{sumcolumn, "Temp"}}),
#"Grouped Rows" = Table.Group(#"Renamed Columns", {}, {{"RunningTotal", each List.Sum([Temp]), type number}}),
RunningTotal = Record.Field(#"Grouped Rows"{0},"RunningTotal")
in
RunningTotal,
AddIndex = Table.TransformColumns
(
Group,
{{"AllRows", (transform) => Table.AddIndexColumn(transform, "Index", 1)}}
),
AddRunSum = Table.TransformColumns
(
AddIndex,
{{"AllRows", (transfom) => Table.AddColumn
(
transfom,
"Run",
(add)=> fnRowIndex(transfom, "SKU Count", add[Index])
)}}
),
AddBin = Table.TransformColumns
(
AddRunSum,
{{"AllRows", (transform) => Table.AddColumn
(
transform,
"Bin used",
(add)=> Number.RoundUp(add[Run]/add[Max Bin])
)}}
),
ExpandAllRows = Table.ExpandTableColumn(AddBin, "AllRows", {"SKU Count", "Max Bin", "Bin used"}, {"SKU Count", "Max Bin", "Bin used"})
/*ExtractBin = Table.AddColumn
(
AddBin,
"Bin used",
each [AllRows][Bin used]
),
#"Hinzugefügter Index" = Table.AddIndexColumn(ExtractBin, "Index", 1, 1),
AddMax = Table.AddColumn
(
#"Hinzugefügter Index",
"Max List",
each List.Max([Bin used])
),
NumberMaxList = Table.TransformColumnTypes(AddMax,{{"Max List", Int64.Type}}),
AddChangedUsedBin = Table.AddColumn
(
NumberMaxList,
/ "New Bin used",
(add) => List.Transform(add[Bin used], (listtransform) => if add[Index] = 1 then listtransform else listtransform + List.Sum
(
Table.SelectRows
(
NumberMaxList, (select)=> select[Index]< add[Index]
)[Max List]
)
)
),
AddNewColumn = Table.AddColumn
(
NumberMaxList,
"Final table",
(add)=> Table.Join(add[AllRows], "Index", Table.AddIndexColumn(Table.FromList(add[New Bin used],Splitter.SplitByNothing(),{"Bin Used"},ExtraValues.Error),"Index1",1),"Index1")
),
DeletedRows = Table.RemoveColumns(AddNewColumn,{"AllRows", "Bin used", "Index", "Max List", "New Bin used"}),
Expand = Table.ExpandTableColumn(DeletedRows, "Final table", {"SKU Count", "Max Bin", "Bin Used"}, {"SKU Count", "Max Bin", "Bin Used"})
*/
in
ExpandAllRows
have a nice evening
Jimmy
:D:D
I don't know if I should laugh o cry
I don't know how you are applying my function.. you have to pass your whole table into the fucntion and using the result not adding columns to multply it 😄
Bye Jimmy
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.