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
hello
for sure everything is possible. But you have to describe it in a more clearer way... so the running sum has to be created by group (whaht group? Column names?)
It would be appreciated to have to workbook and an example of the desired result
Have a nice evening
Jimmy
Sorry!
I'll try to paint a better picture! Maybe I am attacking it in a completely wrong manner! The problem I am solving for is to containerize orders into bins. In the example below there are 2 orders with 6 items each. Order 1 has a total qty of 30 and Order 2 has a total qty of 36. Order 1 can fit into bins with a max size of 12 and Order 2 can fit into bins with a max size of 20. I want to know how many bins order 1 will consume and how many order 2 will consume. I assumed I would need List.Accumulate to accomplish this.
Example:
Order | SKU Count | Max Bin | Running Sum | Expected Results | Bin # |
1 | 5 | 12 | 5 | 5 | 1 |
1 | 5 | 12 | 10 | 10 | 1 |
1 | 5 | 12 | 15 | 5 | 2 |
1 | 5 | 12 | 20 | 10 | 2 |
1 | 5 | 12 | 25 | 5 | 3 |
1 | 5 | 12 | 30 | 10 | 3 |
2 | 6 | 20 | 6 | 6 | 4 |
2 | 6 | 20 | 12 | 12 | 4 |
2 | 6 | 20 | 18 | 18 | 4 |
2 | 6 | 20 | 24 | 6 | 5 |
2 | 6 | 20 | 30 | 12 | 5 |
2 | 6 | 20 | 36 | 18 | 5 |
Hello
didn't get the final result you were looking for. In my solution you can find a new column "Bin used for order" that indicated on every row!! the bins used for that order. I would prefer to have a aggregated view on order level
let
Quelle = Table.FromRows
(
Json.Document
(
Binary.Decompress
(
Binary.FromText
(
"dc7BDcAgCAXQXTh7KKDEXYz7r1GrwEHoASK+fMIYgFCgrULSxx5glovw8ZagBSkaeTBDC3I09uDB709soWjVSOci+sFuLUGqOrVo7KszFNu6cL4=", BinaryEncoding.Base64
),
Compression.Deflate
)
),
let _t = ((type text) meta [Serialized.Text = true]) in type table [Order = _t, #"SKU Count" = _t, #"Max Bin" = _t, #"Running Sum" = _t, #"Expected Results" = _t, #"Bin #" = _t]),
ChangedType = Table.TransformColumnTypes
(
Quelle,
{{"Order", Int64.Type},
{"SKU Count", Int64.Type},
{"Max Bin", Int64.Type},
{"Running Sum", Int64.Type},
{"Expected Results", Int64.Type},
{"Bin #", Int64.Type}}
),
GroupedTable = Table.Group
(
ChangedType,
{"Order", "Max Bin"},
{{"AllRows", each _, type table [Order=number, SKU Count=number, Max Bin=number, Running Sum=number, Expected Results=number, #"Bin #"=number]}}
),
BinUsed = Table.AddColumn(GroupedTable, "Bin used for order", (newColumn)=> Number.Round(List.Sum(newColumn[AllRows][SKU Count])/newColumn[Max Bin],0, RoundingMode.Up)),
ExpandAllRows = Table.ExpandTableColumn(BinUsed, "AllRows", {"SKU Count", "Running Sum", "Expected Results", "Bin #"}, {"SKU Count", "Running Sum", "Expected Results", "Bin #"})
in
ExpandAllRows
have fun
Jimmy
Thank you! I'm not very good with python. How would I modify this to accept my query data?
Hello
my code is simple M-Code
copy it all an paste it in a blank query. Then you will see the result
have fun
Jimmy
Correct, but the sample I provided is just a sample. It is not the full length table that I am transforming. The input data needs to be dynamic. your query is static by nature.
Hello
I provided an example that can be tested by everyone. So if you want to transfer it to a function cut away the table-part and add it as parameter like this
(YourTable as table) as table =>
let
GroupedTable = Table.Group
(
YourTable,
{"Order", "Max Bin"},
{{"AllRows", each _, type table [Order=number, SKU Count=number, Max Bin=number, Running Sum=number, Expected Results=number, #"Bin #"=number]}}
),
BinUsed = Table.AddColumn(GroupedTable, "Bin used for order", (newColumn)=> Number.Round(List.Sum(newColumn[AllRows][SKU Count])/newColumn[Max Bin],0, RoundingMode.Up)),
ExpandAllRows = Table.ExpandTableColumn(BinUsed, "AllRows", {"SKU Count", "Running Sum", "Expected Results", "Bin #"}, {"SKU Count", "Running Sum", "Expected Results", "Bin #"})
in
ExpandAllRows
Sorry for the confusion! But in my example the input data only consists of the Order, SKU Count, and Max Bin. The Bin # is the only real output that matters, the running sum and expected results were built to show the logic as to how the Bin # is asssigned to each order record. Thanks!
hey
so this took me quite a while to figure out. But finally i got it.
Function has to be feed with table with 3 column as stated in your post. Adds "Bin used" as requested
(tTable as table) as table =>
let
Changetype = Table.TransformColumnTypes(tTable,{{"Order", Int64.Type}, {"SKU Count", Int64.Type}, {"Max Bin", Int64.Type}}),
Group = Table.Group(Changetype, {"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])
)}}
),
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 + Table.SelectRows(NumberMaxList, (select)=> select[Index]= add[Index]-1)[Max List]{0})
),
AddNewColumn = Table.AddColumn
(
AddChangedUsedBin,
"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
Expand
Have fun
Jimmy
Thanks Jimmy,
I am getting errors, Orders aren't always integers, can this be switched to text without any reprecussions?
Hello
okay, also this was never stated. Found also another error. But this now should do it.
You owe me a favour, you know? Here the code
(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])
)}}
),
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
(
AddChangedUsedBin,
"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
Expand
Have fun
Jimmy
I definitely owe you a beer or 2! Thank you so much! The results are very close to being right! the difference in complexity between the example data and my actual data is quite large, forgive me for providing erroneous info.
That being said, I may have mispoke when I said do not restart every Order. Can you adjust the code to restart every Order?
Thanks! Here is what I am seeing with my data. Bins Used is your results, Expected Bin is what I mean by restarting with each new Order. It does not appear it is restarting currently. I color coded each Order to visualize the different orders and show the difference between Bin Used and Expected Bin
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
Thank you Jimmy!
The solution works splendidly! Thank you Thank you! It does take quite a while to run when running for hundreds/thousands of records. Do you have any quick tips on how to get the solution to run through the records a little faster?
hey
give it a try to change this part
Group = Table.Group(tTable, {"Order"}, {{"AllRows", each _}}),
fnRowIndex = (tbl as table, sumcolumn as text, rowindex as number) =>
to this
BufferedTable = Table.Buffer(tTable),
Group = Table.Group(BufferedTable , {"Order"}, {{"AllRows", each _}}),
fnRowIndex = (tbl as table, sumcolumn as text, rowindex as number) =>
and let me know
Jimmy
I will add it. But I think I found the main culprit to the run time issue. Before the function I have 816 records, after the function I have over 665,000! I noticed in the last function provided you commented out a Delete Rows step, but there is no Delete Rows step in the uncommented section. Could that be causing the massive increase in rows?
Hello
I don't know what you mean.
I mean I tested it right now with a dataset from 100k. It took less then 15 seconds loading into Power BI. I inputed 100k into the function and i got back excactly the same. So no issue with the function.
Bye
Jimmy
I see what is happening. When I run the function using the base query with the input fields (816 rows) it returns the results 816 times, so when I expand the table it results in 865k rows (816x816).
Last question and I promise I will leave you alone!
If I wanted to pull decriptive columns through the function how would I do that? For example. each line in the input data set represents a SKU #. How could I see SKU # included in the function output?
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.