Date Slot allocation based on slot available and ETA Date

[hpares85]

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.

[v-jingzhang · Accepted 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!

[hpares85]

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. 

[lbendlin]

Please find a proper logistics tool for that.  Power BI has no concept of variables or memory (which would be required for this)