Forum Discussion

Anonymous

Not applicable

6 years ago

Solved

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 t...

Need Help

ImkeF

6 years ago

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.

PBI_Containerization of Orders Function (1).pbix34 KB

Nathaniel_C

Community Champion

6 years ago

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 )

Anonymous

Not applicable

6 years ago

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)
1000	100	10	30	1
1000	200	20	30	1
1000	300	15	30	2
1000	400	17	30	3
2000	100	5	10	1
2000	200	4	10	1
2000	700	7	10	2
2000	800	3	10	2

ImkeF

Community Champion

6 years ago

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.

PBI_Containerization of Orders Function (1).pbix34 KB

Nathaniel_C
Community Champion
6 years ago
ImkeF
Thank you!
Nathaniel
Anonymous
Not applicable
6 years ago
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
  6 years ago
  @lmkeF
  
  One quick follow up question: Can the SKU Qty field be a decimal format?

Nathaniel_C
Community Champion
6 years ago
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.

#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,
- Jimmy801
  Community Champion
  6 years ago
  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

Forum Discussion

Containerization of Orders Function

Related content

V-Order & Z-Order

Ascending order

Top 5 in descending order

Order switching mid workflow

DATESYTD function