Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Containerization of Orders Function

Hi All,

 

I am requesting help to build a function that assigns SKU's within Orders to a container. Each Order uses containers of a certain size which is determined in the Max Bin Qty. SKU Qty is the number of SKU's for that Order. The function should create a running sum for each Order and as that running sum exceeds the Max Bin Qty the running sum will reset to 0 for the next container. Please assume nothing and if you find yourself needing to assume anything please reach out and confirm that assumption! Thank you so much in advance! I know this one is not a typical request and appreciate the help. I've been trying to solve this myself for the last few weeks with bad results and late nights. 

 

 

Order (Function Input)SKU ID (Function Input)SKU Qty (Function Input)Max Bin Qty (Function Input)Container # (Function Output)
100010010301
100020020301
100030015302
100040017303
20005005101
20006004101
20007007102
20008003102

 

1 ACCEPTED SOLUTION

Hi @Anonymous ,

if my understanding is correct, you can use this function on the order-level:  

 

// fnContainers
(Source, MaxBinQtyColName as text, SKUQtyColName as text ) => 
let
//Source = #"myTable (2)"{1}[Partition],
//MaxBinQtyColName = "Max Bin Qty (Function Input)",
//SKUQtyColName = "SKU Qty (Function Input)",
    MaxBinQty = List.First(Table.Column(Source, MaxBinQtyColName)),
    BufferedList = List.Buffer(Table.Column(Source, SKUQtyColName)),
    Output = List.Skip(List.Generate( () =>
                [RT = 0, Bin = 0, Counter = 0],
                each [Counter] <= List.Count(BufferedList),
                each [
                    RT = if  ( [RT] + BufferedList{[Counter]} ) > MaxBinQty then BufferedList{[Counter]} else [RT] + BufferedList{[Counter]} ,
                    Bin = if ( [RT] + BufferedList{[Counter]} ) > MaxBinQty then [Bin] + 1 else [Bin],
                    Counter = [Counter] + 1 
                ],
                each [Bin] + 1

    ))
in
    Output

 

To apply it, your code would look like so (also, see the attached file): 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFDSAVFgEkgYg1lKsTpwSSMoiVXSGKLTFCZphCxpApE0h0kagyWNkO00hdlriCwHsdIEq5w5hITJGSHLWUDchCQXCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Order (Function Input)" = _t, #"SKU ID (Function Input)" = _t, #"SKU Qty (Function Input)" = _t, #"Max Bin Qty (Function Input)" = _t, #"Container # (Function Output)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order (Function Input)", Int64.Type}, {"SKU ID (Function Input)", Int64.Type}, {"SKU Qty (Function Input)", Int64.Type}, {"Max Bin Qty (Function Input)", Int64.Type}, {"Container # (Function Output)", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Order (Function Input)"}, {{"Partition", each _}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "Container", each fnContainers([Partition], "Max Bin Qty (Function Input)", "SKU Qty (Function Input)")),
    #"Added Custom" = Table.AddColumn(  #"Invoked Custom Function", 
                                        "Result", 
                                        each Table.FromColumns(Table.ToColumns([Partition]) & {[Container]}, Table.ColumnNames([Partition]) & {"Container"})),
    Custom1 = Table.Combine(#"Added Custom"[Result])
in
    Custom1

This technique should also work fairly fast on large tables.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

14 REPLIES 14
Nathaniel_C
Super User
Super User

Hi @Anonymous ,
Thank you for providing your sample data. Based on that data would you also posted your expected outcome?
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@Nathaniel_C  In the Sample Data the Headers display the columns that are input columns and what column that shows the expected outcome of the function. The function should only create 1 new column assigning a container number to each SKU within an Order. 

Hi @Anonymous ,
So another way to look at this would be, a running total of how many containers are needed to fill an order? We are not really assigning a container # to a SKU, because in some cases, a SKU might go into more than 1 container, correct?

Will be offline for awhile...
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@Nathaniel_C  That is correct, Keeping the results at the SKU level (meaning Container # will repat itself if an Order has multiple SKU's). You will not need to break out a SKU across multiple containers. Said another way, you will not need to break a record out into 2 records to fill up 1 container and start another. If the entire SKU Qty cannot fit into the remaining capacity of a container then a new container is assigned to that SKU. 

Hi @Anonymous 
This solution is based on your data submitted. I used your SKU ID to know my place in the table.  You may need to add an Index column in Power Query to perform that function instead.
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Sum of SKU QTY = SUM(myTable[SKU Qty (Function Input)])

 

RT of SKU QTY =
VAR _SKU_ID =
    MAX ( myTable[SKU ID (Function Input)] )
VAR _order =
    MAX ( myTable[Order (Function Input)] )
VAR _SKU_QTY =
    MAX ( myTable[SKU Qty (Function Input)] )
VAR _maxBinQty =
    MAX ( myTable[Max Bin Qty (Function Input)] )
VAR _calc =
    CALCULATE (
        [Sum of SKU QTY],
        ALLEXCEPT ( myTable, myTable[Order (Function Input)] ),
        myTable[SKU ID (Function Input)] <= _SKU_ID
    )
RETURN
    ROUNDUP ( DIVIDE ( _calc, _maxBinQty ), 0 )

 

RT of Sku.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@Nathaniel_C  Thank you for the quick response! 

 

I have 2 follow up questions / comments:

 

1. Can this be done as an M code function because I have multiple tables I'd like to apply this logic to then additional transformations to be completed. Completing this step in DAX will not allow me to complete these extra steps (Hence why I placed this question in the Power Query Section).

 

2. Using SKU ID cannot be used to keep track of where you are because SKU ID can be duplicated in multiple Orders. I've modified the example data set to show you what I mean (Both orders now have SKU's 100 and 200)

 

3.  Your logic does not appear to allow the logic to restart with every new order. The values being assigned to each variable span the entire data set, When they should only span the SKU's within each new Order. See in the example below how when the function gets to Order 2000 the container starts over at 1. 

 

Order (Function Input)SKU ID (Function Input)SKU Qty (Function Input)Max Bin Qty (Function Input)Container # (Function Output)
100010010301
100020020301
100030015302
100040017303
20001005101
20002004101
20007007102
20008003102

Hi @Anonymous ,

if my understanding is correct, you can use this function on the order-level:  

 

// fnContainers
(Source, MaxBinQtyColName as text, SKUQtyColName as text ) => 
let
//Source = #"myTable (2)"{1}[Partition],
//MaxBinQtyColName = "Max Bin Qty (Function Input)",
//SKUQtyColName = "SKU Qty (Function Input)",
    MaxBinQty = List.First(Table.Column(Source, MaxBinQtyColName)),
    BufferedList = List.Buffer(Table.Column(Source, SKUQtyColName)),
    Output = List.Skip(List.Generate( () =>
                [RT = 0, Bin = 0, Counter = 0],
                each [Counter] <= List.Count(BufferedList),
                each [
                    RT = if  ( [RT] + BufferedList{[Counter]} ) > MaxBinQty then BufferedList{[Counter]} else [RT] + BufferedList{[Counter]} ,
                    Bin = if ( [RT] + BufferedList{[Counter]} ) > MaxBinQty then [Bin] + 1 else [Bin],
                    Counter = [Counter] + 1 
                ],
                each [Bin] + 1

    ))
in
    Output

 

To apply it, your code would look like so (also, see the attached file): 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQwMFDSAVFgEkgYg1lKsTpwSSMoiVXSGKLTFCZphCxpApE0h0kagyWNkO00hdlriCwHsdIEq5w5hITJGSHLWUDchCQXCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Order (Function Input)" = _t, #"SKU ID (Function Input)" = _t, #"SKU Qty (Function Input)" = _t, #"Max Bin Qty (Function Input)" = _t, #"Container # (Function Output)" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order (Function Input)", Int64.Type}, {"SKU ID (Function Input)", Int64.Type}, {"SKU Qty (Function Input)", Int64.Type}, {"Max Bin Qty (Function Input)", Int64.Type}, {"Container # (Function Output)", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Order (Function Input)"}, {{"Partition", each _}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Grouped Rows", "Container", each fnContainers([Partition], "Max Bin Qty (Function Input)", "SKU Qty (Function Input)")),
    #"Added Custom" = Table.AddColumn(  #"Invoked Custom Function", 
                                        "Result", 
                                        each Table.FromColumns(Table.ToColumns([Partition]) & {[Container]}, Table.ColumnNames([Partition]) & {"Container"})),
    Custom1 = Table.Combine(#"Added Custom"[Result])
in
    Custom1

This technique should also work fairly fast on large tables.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

@ImkeF  this is phenomenal! truly artistic coding, This solves the problem perfectly! Thank you so much! 

@Nathaniel_C Thank you for facilitating and being patient with me!

@Jimmy801 Thank you again for your attempts to solve this problem. A second post was exactly what was needed. 

Anonymous
Not applicable

@lmkeF

One quick follow up question: Can the SKU Qty field be a decimal format?

Hi @Anonymous 

I see no reason why not. Or am I missing sth here?

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

I didn't think it would matter (and I just confirmed it does not). I just wanted to be sure. Thank you again!

@ImkeF 
Thank you!
Nathaniel





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @Anonymous ,
You are welcome!


Item #3, if you look at my previous picture, it does restart for each order due to the ALLEXCEPT(). 
Item #2 internally for each order per your example, this will work because it checks for each order, but as I mentioned in my pm you may need to add an index column in Power Query for other data. Below is a picture with your new data showing the correct results.RT of Sku1.PNG

 

#1 I would like to suggest that you mention you would like this in m language in the future in any post.  Folks post their questions all over this board. In any case perhaps we can ask @ImkeF the magician of m if this can be done in m.

Here is my pbix for this post 

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel



Item #1, 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hello

 

i have to write this to prevent somebody other again loosing time

this is again going to be ridiculous.

I tried to help to the same topic, lost time after time to only understand at the end that some "minor" issues were not mentioned.

I made the same proposal as @Nathaniel_C, and neither works.

So if somebody will help here, consider, that this was already treated in very detail within here link 

 

Good luck to whom that still is willing to help

@Anonymous- please, don't opend a topic twice.

 

Bye

Jimmy

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors