Forum Discussion
How to get delivery date for order table from inventory table and get updated inventory?
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 |
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.
7 Replies
Power BI is not an inventory management tool. You may want to do these computations in the source system.
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.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?
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.
