How to get delivery date for order table from inventory table and get updated inventory?

[xzmiche]

I cannot find a Power Query solution to get delivery date for order table from inventory table and get updated inventory. Can anyone help me on that?

My data:

Input:

In_Order_Table:

Order Id	Product
1	p1
2	p2
3	p1
4	p3
5	p2

In_Inventory_Table:

Product	Date	Qty
p1	01-May	1
p1	02-May	2
p2	01-May	2
p3	03-May	1
p1	03-May	2

Output:

Out_Order_Table:

Order Id	Product	Delivery Date
1	p1	01-May
2	p2	01-May
3	p1	02-May
4	p3	03-May
5	p2	01-May

Out_Inventory_Table:

Product	Date	Qty
p1	01-May	0
p1	02-May	1
p2	01-May	0
p3	03-May	0
p1	03-May	2

[v-yingjl]

Hi @xzmiche ,

If the Delivery Date column is added based on the In_Order_Table, you can try this query on the In_Inventory_Table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKjBU0lEyMNT1TawEMgyVYnVgYkZQMSOImBGyOqiYMUjMGIteY4S6WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Date = _t, Qty = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Date", type date}, {"Qty", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Product", "Date"}, In_Order_Table, {"Product", "Delivery Date"}, "In_Order_Table", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(#"Merged Queries", "Custom", each [Qty] - Table.RowCount([In_Order_Table]),Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Qty", "In_Order_Table"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Qty"}})
in
    #"Renamed Columns"

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[xzmiche]

Thanks Yingjie!

Your answer is exactly the question 2 based on table Out_Order_Table and In_Inventory_Table to generate Out_Inventory_Table. Would you please help on studying question1 to generate Out_Order_Table based on In_Order_Table and In_Inventory_Table?

[v-yingjl]

Hi @xzmiche ,

If just based on the source two tables, you need to add a column 'oder id' in 'In_Inventory_Table' in order to combine them based on the id like this:

Since there is no clear logic about the null date after merging tables, need to define a logic manually in the query like this to get 'Out_Order_Table':

let
    Source = Table.NestedJoin(In_Order_Table, {"Order Id", "Product"}, In_Inventory_Table, {"Order Id", "Product"}, "In_Inventory_Table", JoinKind.LeftOuter),
    #"Expanded In_Inventory_Table" = Table.ExpandTableColumn(Source, "In_Inventory_Table", {"Date"}, {"In_Inventory_Table.Date"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded In_Inventory_Table",null,List.Min(#"Expanded In_Inventory_Table"[In_Inventory_Table.Date]),Replacer.ReplaceValue,{"In_Inventory_Table.Date"}),
    #"Renamed Columns" = Table.RenameColumns(#"Replaced Value",{{"In_Inventory_Table.Date", "Delivery Date"}})
in
    #"Renamed Columns"

'Out_Inventory_Table' query:

let
    Source = Table.NestedJoin(In_Inventory_Table, {"Product", "Date"}, Out_Order_Table, {"Product", "Delivery Date"}, "Out_Order_Table", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each [Qty] - Table.RowCount([Out_Order_Table])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Qty", "Order Id", "Out_Order_Table"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Qty"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Product", type text}, {"Date", type date}, {"Qty", Int64.Type}})
in
    #"Changed Type"

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[xzmiche]

Thanks Yingjie,

Sorry to confuse you! Let me clarify my logic as below:

1. we select one row from "In_Order_Table"

2. find earliest date in "In_Inventory_Talbe"

3. put this date in "Out_Order_Table"

4. at same time we reduce qty in "Out_Inventory_table"

5. repeat 1-4 until all orders dealed

By above steps, we can get "Out_Order_Table" and "Out_Inventory_Table". So we cannot add product id in "In_Inventory_Table".

[v-yingjl · Accepted solution]

Hi @xzmiche ,

If cannot add product id in "In_Inventory_Table", still need a reference column like an index column to query.

For "In_Order_Table":

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUSowVIrViVYyAjGNwExjhKgJiGkMZppCFcQCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Order Id" = _t, Product = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Id", Int64.Type}, {"Product", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product"}, {{"Data", each Table.AddIndexColumn(_,"Index",1,1), type table [Order Id=nullable number, Product=nullable text]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Order Id","Index"}, {"Order Id","Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Data",{{"Product", type text}, {"Order Id", Int64.Type}, {"Index", Int64.Type}})
in
    #"Changed Type1"

For "In_Inventory_Talbe":

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKjBU0lEyMNT1TawEMgyVYnVgYkZQMSOImBGyOqiYMUjMGIteY4S6WAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Product = _t, Date = _t, Qty = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Date", type date}, {"Qty", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product"}, {{"Data", each Table.AddIndexColumn(_,"Index",1,1,Int64.Type), type table [Product=nullable text, Date=nullable date, Qty=nullable number]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Date", "Qty","Index"}, {"Date", "Qty","Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Data",{{"Index", Int64.Type}})
in
    #"Changed Type1"

"Out_Order_Table" query:

let
    Source = Table.NestedJoin(In_Order_Table, {"Product", "Index"}, In_Inventory_Table, {"Product", "Index"}, "In_Inventory_Table", JoinKind.LeftOuter),
    #"Sorted Rows" = Table.Sort(Source,{{"Order Id", Order.Ascending}}),
    #"Expanded In_Inventory_Table" = Table.ExpandTableColumn(#"Sorted Rows", "In_Inventory_Table", {"Date"}, {"In_Inventory_Table.Date"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded In_Inventory_Table",null,List.Min(#"Expanded In_Inventory_Table"[In_Inventory_Table.Date]),Replacer.ReplaceValue,{"In_Inventory_Table.Date"}),
    #"Removed Columns" = Table.RemoveColumns(#"Replaced Value",{"Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"In_Inventory_Table.Date", "Delivery Date"}})
in
    #"Renamed Columns"

 "Out_Inventory_table" query:

let
    Source = Table.NestedJoin(In_Inventory_Table, {"Product", "Date"}, Out_Order_Table, {"Product", "Delivery Date"}, "Out_Order_Table", JoinKind.LeftOuter),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each [Qty] - Table.RowCount([Out_Order_Table])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Qty", "Index", "Out_Order_Table"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Qty"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Product", type text}, {"Date", type date}, {"Qty", Int64.Type}})
in
    #"Changed Type"

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[xzmiche]

Excellent! That really solved my concerns by adding 2 indexes in 2 input tables.

[lbendlin]

Power BI is not an inventory management tool.  You may want to do these computations in the source system.