Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I have 2 table. 1 table is my warehouses daily available slot to receive goods. Another table is the goods ETA at port. We already preplanned which goods is to which warehouse. But I need a query to match the time slot table to goods ETA table. whichever ETA reached first will take earliest slot based on warehouse, but some of the earliest slot available is earlier than ETA. Any suggestion? Thanks.
Solved! Go to Solution.
Hi @hpares85
You can create a query with below M code. I have attached the sample file at bottom for your reference.
let
Source = Table.NestedJoin(#"GOODS ETA TABLE", {"PLANNED WAREHOUSE"}, #"TIME SLOT TABLE", {"WAREHOUSE"}, "TIME SLOT TABLE", JoinKind.LeftOuter),
#"Added Custom" = Table.AddColumn(Source, "Custom", each let __ETA = [ETA] in Table.FirstN(Table.Sort(Table.SelectRows([TIME SLOT TABLE], each [TIME SLOT] > __ETA), {{"TIME SLOT", Order.Ascending}}),1)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"GOODS", "ETA", "Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"TIME SLOT", "WAREHOUSE"}, {"TIME SLOT", "WAREHOUSE"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([WAREHOUSE] <> null)),
#"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"GOODS", "ETA", "WAREHOUSE", "TIME SLOT"})
in
#"Reordered Columns"
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!
Hi @hpares85
You can create a query with below M code. I have attached the sample file at bottom for your reference.
let
Source = Table.NestedJoin(#"GOODS ETA TABLE", {"PLANNED WAREHOUSE"}, #"TIME SLOT TABLE", {"WAREHOUSE"}, "TIME SLOT TABLE", JoinKind.LeftOuter),
#"Added Custom" = Table.AddColumn(Source, "Custom", each let __ETA = [ETA] in Table.FirstN(Table.Sort(Table.SelectRows([TIME SLOT TABLE], each [TIME SLOT] > __ETA), {{"TIME SLOT", Order.Ascending}}),1)),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"GOODS", "ETA", "Custom"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"TIME SLOT", "WAREHOUSE"}, {"TIME SLOT", "WAREHOUSE"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Custom", each ([WAREHOUSE] <> null)),
#"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"GOODS", "ETA", "WAREHOUSE", "TIME SLOT"})
in
#"Reordered Columns"
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!
TIME SLOT WAREHOUSE
23/12/2022 | A |
23/12/2022 | A |
23/12/2022 | A |
23/12/2022 | A |
23/12/2022 | A |
23/12/2022 | B |
23/12/2022 | B |
24/12/2022 | A |
24/12/2022 | A |
24/12/2022 | A |
24/12/2022 | A |
24/12/2022 | A |
24/12/2022 | B |
24/12/2022 | B |
29/12/2022 | A |
29/12/2022 | A |
29/12/2022 | A |
29/12/2022 | A |
29/12/2022 | A |
29/12/2022 | B |
29/12/2022 | B |
30/12/2022 | A |
31/12/2022 | A |
31/12/2022 | A |
31/12/2022 | A |
31/12/2022 | A |
31/12/2022 | B |
31/12/2022 | B |
03/01/2023 | A |
03/01/2023 | A |
03/01/2023 | A |
03/01/2023 | A |
03/01/2023 | A |
03/01/2023 | B |
03/01/2023 | B |
03/01/2023 | B |
04/01/2023 | A |
04/01/2023 | A |
GOODS ETA
1001 | 04/12/2022 | A |
1002 | 04/12/2022 | A |
1003 | 01/12/2022 | A |
1004 | 01/12/2022 | A |
1005 | 01/12/2022 | A |
1006 | 01/12/2022 | A |
1007 | 10/12/2022 | A |
1008 | 25/12/2022 | A |
1009 | 25/12/2022 | A |
1010 | 25/12/2022 | A |
1011 | 25/12/2022 | A |
1012 | 25/12/2022 | A |
1013 | 25/12/2022 | A |
1014 | 25/12/2022 | A |
1015 | 25/12/2022 | A |
1016 | 28/12/2022 | A |
1017 | 28/12/2022 | A |
1018 | 28/12/2022 | A |
1019 | 28/12/2022 | A |
1020 | 28/12/2022 | A |
1021 | 28/12/2022 | A |
1022 | 28/12/2022 | A |
Result
1001 | 04/12/2022 | A | 23/12/2022 |
1002 | 04/12/2022 | A | 23/12/2022 |
1003 | 01/12/2022 | A | 23/12/2022 |
1004 | 01/12/2022 | A | 23/12/2022 |
1005 | 01/12/2022 | A | 23/12/2022 |
1006 | 01/12/2022 | A | 23/12/2022 |
1007 | 10/12/2022 | A | 23/12/2022 |
1008 | 25/12/2022 | A | 29/12/2022 |
1009 | 25/12/2022 | A | 29/12/2022 |
1010 | 25/12/2022 | A | 29/12/2022 |
1011 | 25/12/2022 | A | 29/12/2022 |
1012 | 25/12/2022 | A | 29/12/2022 |
1013 | 25/12/2022 | A | 29/12/2022 |
1014 | 25/12/2022 | A | 29/12/2022 |
1015 | 25/12/2022 | A | 29/12/2022 |
1016 | 28/12/2022 | A | 29/12/2022 |
1017 | 28/12/2022 | A | 29/12/2022 |
1018 | 28/12/2022 | A | 29/12/2022 |
1019 | 28/12/2022 | A | 29/12/2022 |
1020 | 28/12/2022 | A | 29/12/2022 |
1021 | 28/12/2022 | A | 29/12/2022 |
1022 | 28/12/2022 | A | 29/12/2022 |
Thanks but not fully working. Because A warehouse on 23/12/2022, there is only 5 Slot Available. But the result allocated 7 slot for warehouse A on 23/12/2022.
Please find a proper logistics tool for that. Power BI has no concept of variables or memory (which would be required for this)