Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
hpares85
New Member

Date Slot allocation based on slot available and ETA Date

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.

 

hpares85_0-1672373261798.png

 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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"

vjingzhang_1-1672712928471.png

 

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!

View solution in original post

3 REPLIES 3
v-jingzhang
Community Support
Community Support

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"

vjingzhang_1-1672712928471.png

 

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/2022A
23/12/2022A
23/12/2022A
23/12/2022A
23/12/2022A
23/12/2022B
23/12/2022B
24/12/2022A
24/12/2022A
24/12/2022A
24/12/2022A
24/12/2022A
24/12/2022B
24/12/2022B
29/12/2022A
29/12/2022A
29/12/2022A
29/12/2022A
29/12/2022A
29/12/2022B
29/12/2022B
30/12/2022A
31/12/2022A
31/12/2022A
31/12/2022A
31/12/2022A
31/12/2022B
31/12/2022B
03/01/2023A
03/01/2023A
03/01/2023A
03/01/2023A
03/01/2023A
03/01/2023B
03/01/2023B
03/01/2023B
04/01/2023A
04/01/2023A

 

GOODS ETA

100104/12/2022A
100204/12/2022A
100301/12/2022A
100401/12/2022A
100501/12/2022A
100601/12/2022A
100710/12/2022A
100825/12/2022A
100925/12/2022A
101025/12/2022A
101125/12/2022A
101225/12/2022A
101325/12/2022A
101425/12/2022A
101525/12/2022A
101628/12/2022A
101728/12/2022A
101828/12/2022A
101928/12/2022A
102028/12/2022A
102128/12/2022A
102228/12/2022A

 

Result

100104/12/2022A23/12/2022
100204/12/2022A23/12/2022
100301/12/2022A23/12/2022
100401/12/2022A23/12/2022
100501/12/2022A23/12/2022
100601/12/2022A23/12/2022
100710/12/2022A23/12/2022
100825/12/2022A29/12/2022
100925/12/2022A29/12/2022
101025/12/2022A29/12/2022
101125/12/2022A29/12/2022
101225/12/2022A29/12/2022
101325/12/2022A29/12/2022
101425/12/2022A29/12/2022
101525/12/2022A29/12/2022
101628/12/2022A29/12/2022
101728/12/2022A29/12/2022
101828/12/2022A29/12/2022
101928/12/2022A29/12/2022
102028/12/2022A29/12/2022
102128/12/2022A29/12/2022
102228/12/2022A29/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
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors