Create Equally Distributed Deliverables Table

Pestomania · Sep 29, 2023

Hello all!!!

I am trying to take a few tables: deliverables per year, item codes by program, and lot size by program into one large equally distributed deliverables table. Below is a mini sheet with similar details to what I am working on. I am unsure if this can be completed by formula or if macro is necessary (either is great). The actual equally distributed table is about 3000 lines (I created it manually the first time, don't want to again).

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

1

Program

Customer Name

Year

Quantity

Unit Price

Under Contract

Program

Lot Size

Item Code

Program

Date

Item Code

Quantity

Expected Revenue

2

Program 1

Customer 1

2024

1

$ 140.00

Yes

Program 1

1

123

Program 1

1/2/2025

123

1

800

Expected Revenue = Qty per month * Unit Price by Year

3

Program 1

Customer 1

2025

1

$ 800.00

No

Program 2

6

456

Program 1

1/2/2026

123

1

1000

Date is an equal distribution of lot size deliverables across the year. So 30 deliverables with lot size of 6 = 6 delivered every 3 months.

4

Program 1

Customer 1

2026

1

$ 1,000.00

No

Program 3

2

789

Program 1

1/2/2027

123

1

1000

Item Code = vlookup of program name to the item code table

5

Program 1

Customer 1

2027

1

$ 1,000.00

No

Program 1

1/2/2028

123

1

1000

If Under Contract = Yes, do not model.

6

Program 1

Customer 1

2028

1

$ 1,000.00

No

Program 2

1/2/2024

456

6

1280

7

Program 2

Customer 2

2024

30

$ 640.00

No

Program 2

4/1/2024

456

6

1280

8

Program 2

Customer 2

2025

30

$ 640.00

No

Program 2

6/30/2024

456

6

1280

9

Program 2

Customer 2

2026

30

$ 640.00

No

Program 2

9/28/2024

456

6

1280

10

Program 2

Customer 2

2027

30

$ 640.00

No

Program 2

12/27/2024

456

6

1280

11

Program 2

Customer 2

2028

30

$ 640.00

No

Program 2

1/2/2025

456

6

1280

12

Program 3

Customer 1

2024

24

$ 379.00

Yes

Program 2

4/1/2025

456

6

1280

13

Program 3

Customer 1

2025

32

$ 288.00

Yes

Program 2

6/29/2025

456

6

1280

14

Program 3

Customer 1

2026

40

$ 293.00

No

Program 2

9/26/2025

456

6

1280

15

Program 3

Customer 1

2027

40

$ 330.00

No

Program 2

12/24/2025

456

6

1280

16

Program 3

Customer 1

2028

40

$ 330.00

No

Program 2

1/2/2026

456

6

1280

17

Program 2

4/1/2026

456

6

1280

18

Program 2

6/29/2026

456

6

1280

19

Program 2

9/26/2026

456

6

1280

20

Program 2

12/24/2026

456

6

1280

21

Program 2

1/2/2026

456

6

1280

22

Program 2

4/1/2026

456

6

1280

23

Program 2

6/29/2026

456

6

1280

24

Program 2

9/26/2026

456

6

1280

25

Program 2

12/24/2026

456

6

1280

26

Program 2

1/2/2027

456

6

1280

27

Program 2

4/1/2027

456

6

1280

28

Program 2

6/29/2027

456

6

1280

29

Program 2

9/26/2027

456

6

1280

30

Program 2

12/24/2027

456

6

1280

31

Program 2

1/2/2028

456

6

1280

32

Program 2

4/1/2028

456

6

1280

33

Program 2

6/29/2028

456

6

1280

34

Program 2

9/26/2028

456

6

1280

35

Program 2

12/24/2028

456

6

1280

36

Program 3

1/1/2026

789

2

586

37

Program 3

1/20/2026

789

2

586

38

Program 3

2/8/2026

789

2

586

39

Program 3

2/27/2026

789

2

586

40

Program 3

3/18/2026

789

2

586

41

Program 3

4/6/2026

789

2

586

42

Program 3

4/25/2026

789

2

586

43

Program 3

5/14/2026

789

2

586

44

Program 3

6/2/2026

789

2

586

45

Program 3

6/21/2026

789

2

586

46

Program 3

7/10/2026

789

2

586

47

Program 3

7/29/2026

789

2

586

48

Program 3

8/17/2026

789

2

586

49

Program 3

9/5/2026

789

2

586

50

Program 3

9/24/2026

789

2

586

51

Program 3

10/13/2026

789

2

586

52

Program 3

11/1/2026

789

2

586

53

Program 3

11/20/2026

789

2

586

54

Program 3

12/9/2026

789

2

586

55

Program 3

12/28/2026

789

2

586

56

Program 3

1/1/2027

789

2

660

57

Program 3

1/20/2027

789

2

660

58

Program 3

2/8/2027

789

2

660

59

Program 3

2/27/2027

789

2

660

60

Program 3

3/18/2027

789

2

660

61

Program 3

4/6/2027

789

2

660

62

Program 3

4/25/2027

789

2

660

63

Program 3

5/14/2027

789

2

660

64

Program 3

6/2/2027

789

2

660

65

Program 3

6/21/2027

789

2

660

66

Program 3

7/10/2027

789

2

660

67

Program 3

7/29/2027

789

2

660

68

Program 3

8/17/2027

789

2

660

69

Program 3

9/5/2027

789

2

660

70

Program 3

9/24/2027

789

2

660

71

Program 3

10/13/2027

789

2

660

72

Program 3

11/1/2027

789

2

660

73

Program 3

11/20/2027

789

2

660

74

Program 3

12/9/2027

789

2

660

75

Program 3

12/28/2027

789

2

660

76

Program 3

1/1/2028

789

2

660

77

Program 3

1/20/2028

789

2

660

78

Program 3

2/8/2028

789

2

660

79

Program 3

2/27/2028

789

2

660

80

Program 3

3/17/2028

789

2

660

81

Program 3

4/5/2028

789

2

660

82

Program 3

4/24/2028

789

2

660

83

Program 3

5/13/2028

789

2

660

84

Program 3

6/1/2028

789

2

660

85

Program 3

6/20/2028

789

2

660

86

Program 3

7/9/2028

789

2

660

87

Program 3

7/28/2028

789

2

660

88

Program 3

8/16/2028

789

2

660

89

Program 3

9/4/2028

789

2

660

90

Program 3

9/23/2028

789

2

660

91

Program 3

10/12/2028

789

2

660

92

Program 3

10/31/2028

789

2

660

93

Program 3

11/19/2028

789

2

660

94

Program 3

12/8/2028

789

2

660

95

Program 3

12/27/2028

789

2

660

Sheet1

Cell Formulas
Range	Formula
Q36:Q55	Q36	=293*P36

KRice · Oct 7, 2023

I had a look at this problem and have an approach that uses Power Query. Your two input tables are first converted to formal Excel tables (click anywhere in the table's range and hit Ctrl-t, which opens a dialog box where you would confirm that your table has headers...and then confirm with OK or Enter). I accepted Excel's suggested names for these tables: Table1 and Table2, but that can be changed under Table Design. After filtering Table1 to keep only those rows that have "No" for the [Under Contract] column, these two tables are merged in the SummaryTable query based on having a common [Program].

You did not mention anything about holidays, but it appears that you might want to avoid deliveries on holidays? On a separate worksheet named Holidays, I created a formal table of holidays (for this example, I adopted U.S. federal holidays, but the table is readily edited to accommodate others). Of note, the language used in the Holidays table matters. As many holidays occur on some day of the week in some week of a month, rather than on a specific date, such holidays need to be described in terms of the First, Second (and so on) Monday, Tuesday (and so on) "of" January, February (and so on). Then given a particular year, that text string is parsed to return the specific date during that year when the holiday occurs. For holidays that fall on a Saturday or Sunday, for workday purposes, those holidays are often celebrated on Friday (for Saturday holidays) or Monday (for Sunday holidays), so another rule is implemented to add "Celebrated On" dates to the list of holiday dates that should be excluded from the list of dates when deliveries could occur. All of this is done in the HolidaysList query and used in the Networkdays2 and DeliveryDates functions described next.

The Networkdays2 function creates a list of every date in the calendar year, and then based on user-selectable options, days associated with weekends and holidays can be excluded. In this example, I chose Saturday and Sunday as the weekend and used the HolidaysList[Celebrated On] field to trim the work day list. This function is called by the DeliveryDates function described next.

The DeliveryDates function takes input arguments from the original merged source tables: Year, Quantity, and LotSize (as well as HolidaysList[Celebrated On]). Then based on the count of days available for deliveries during the calendar year and the number of deliveries necessary (Quantity/LotSize), equal intervals of workdays are computed, and from that, the delivery dates are determined. These dates are similar to those in your example, although I am not sure if you are applying delivery date restrictions that differ from those that I have described.

I do not follow your Expected Revenue computations. You have described it as "Expected Revenue = Qty per month * Unit Price by Year", but I am not sure what that means. All of your "Expected Revenue" values are one or two times the "Unit Price", which does not seem to correspond to Lot Sizes, Number of Deliveries, or Delivery Interval Sizes. Could you please confirm your Expected Revenue values and explain how they are to be calculated?

The final table is output to the Results Table worksheet, although this version omits Expected Revenue pending further details. Because this approach involves setting up several queries/functions in Power Query, I've uploaded a working version here:

Dropbox

www.dropbox.com

Pestomania · Nov 22, 2023

KRice said:
I had a look at this problem and have an approach that uses Power Query. Your two input tables are first converted to formal Excel tables (click anywhere in the table's range and hit Ctrl-t, which opens a dialog box where you would confirm that your table has headers...and then confirm with OK or Enter). I accepted Excel's suggested names for these tables: Table1 and Table2, but that can be changed under Table Design. After filtering Table1 to keep only those rows that have "No" for the [Under Contract] column, these two tables are merged in the SummaryTable query based on having a common [Program].

You did not mention anything about holidays, but it appears that you might want to avoid deliveries on holidays? On a separate worksheet named Holidays, I created a formal table of holidays (for this example, I adopted U.S. federal holidays, but the table is readily edited to accommodate others). Of note, the language used in the Holidays table matters. As many holidays occur on some day of the week in some week of a month, rather than on a specific date, such holidays need to be described in terms of the First, Second (and so on) Monday, Tuesday (and so on) "of" January, February (and so on). Then given a particular year, that text string is parsed to return the specific date during that year when the holiday occurs. For holidays that fall on a Saturday or Sunday, for workday purposes, those holidays are often celebrated on Friday (for Saturday holidays) or Monday (for Sunday holidays), so another rule is implemented to add "Celebrated On" dates to the list of holiday dates that should be excluded from the list of dates when deliveries could occur. All of this is done in the HolidaysList query and used in the Networkdays2 and DeliveryDates functions described next.

The Networkdays2 function creates a list of every date in the calendar year, and then based on user-selectable options, days associated with weekends and holidays can be excluded. In this example, I chose Saturday and Sunday as the weekend and used the HolidaysList[Celebrated On] field to trim the work day list. This function is called by the DeliveryDates function described next.

The DeliveryDates function takes input arguments from the original merged source tables: Year, Quantity, and LotSize (as well as HolidaysList[Celebrated On]). Then based on the count of days available for deliveries during the calendar year and the number of deliveries necessary (Quantity/LotSize), equal intervals of workdays are computed, and from that, the delivery dates are determined. These dates are similar to those in your example, although I am not sure if you are applying delivery date restrictions that differ from those that I have described.

I do not follow your Expected Revenue computations. You have described it as "Expected Revenue = Qty per month * Unit Price by Year", but I am not sure what that means. All of your "Expected Revenue" values are one or two times the "Unit Price", which does not seem to correspond to Lot Sizes, Number of Deliveries, or Delivery Interval Sizes. Could you please confirm your Expected Revenue values and explain how they are to be calculated?

The final table is output to the Results Table worksheet, although this version omits Expected Revenue pending further details. Because this approach involves setting up several queries/functions in Power Query, I've uploaded a working version here:

Dropbox

www.dropbox.com

Hi @KRice, apologies for the delay in reviewing this but I had projects take my time up. I unfortunately cannot access the dropbox file but your response sounds like it would be extremely helpful.

Do you know if it is possible to get it through a different method?

Pestomania · Nov 22, 2023

KRice said:
I had a look at this problem and have an approach that uses Power Query. Your two input tables are first converted to formal Excel tables (click anywhere in the table's range and hit Ctrl-t, which opens a dialog box where you would confirm that your table has headers...and then confirm with OK or Enter). I accepted Excel's suggested names for these tables: Table1 and Table2, but that can be changed under Table Design. After filtering Table1 to keep only those rows that have "No" for the [Under Contract] column, these two tables are merged in the SummaryTable query based on having a common [Program].

You did not mention anything about holidays, but it appears that you might want to avoid deliveries on holidays? On a separate worksheet named Holidays, I created a formal table of holidays (for this example, I adopted U.S. federal holidays, but the table is readily edited to accommodate others). Of note, the language used in the Holidays table matters. As many holidays occur on some day of the week in some week of a month, rather than on a specific date, such holidays need to be described in terms of the First, Second (and so on) Monday, Tuesday (and so on) "of" January, February (and so on). Then given a particular year, that text string is parsed to return the specific date during that year when the holiday occurs. For holidays that fall on a Saturday or Sunday, for workday purposes, those holidays are often celebrated on Friday (for Saturday holidays) or Monday (for Sunday holidays), so another rule is implemented to add "Celebrated On" dates to the list of holiday dates that should be excluded from the list of dates when deliveries could occur. All of this is done in the HolidaysList query and used in the Networkdays2 and DeliveryDates functions described next.

The Networkdays2 function creates a list of every date in the calendar year, and then based on user-selectable options, days associated with weekends and holidays can be excluded. In this example, I chose Saturday and Sunday as the weekend and used the HolidaysList[Celebrated On] field to trim the work day list. This function is called by the DeliveryDates function described next.

The DeliveryDates function takes input arguments from the original merged source tables: Year, Quantity, and LotSize (as well as HolidaysList[Celebrated On]). Then based on the count of days available for deliveries during the calendar year and the number of deliveries necessary (Quantity/LotSize), equal intervals of workdays are computed, and from that, the delivery dates are determined. These dates are similar to those in your example, although I am not sure if you are applying delivery date restrictions that differ from those that I have described.

I do not follow your Expected Revenue computations. You have described it as "Expected Revenue = Qty per month * Unit Price by Year", but I am not sure what that means. All of your "Expected Revenue" values are one or two times the "Unit Price", which does not seem to correspond to Lot Sizes, Number of Deliveries, or Delivery Interval Sizes. Could you please confirm your Expected Revenue values and explain how they are to be calculated?

The final table is output to the Results Table worksheet, although this version omits Expected Revenue pending further details. Because this approach involves setting up several queries/functions in Power Query, I've uploaded a working version here:

Dropbox

www.dropbox.com

Hi @KRice, Sorry for the delay in reviewing this but thank you for putting this together. I am unable to access the dropbox file but it sounds perfect for what I need.

Is there another way to access this file?

KRice · Nov 22, 2023

Do you have any idea why you can't access the file? Do you have corporate firewall restrictions that prevent you from accessing Dropbox? Here is the link again...but if restrictions are on your end, this won't help anyway.

Dropbox

www.dropbox.com

It shouldn't be too difficult to set this up using the cut-paste features in the XL2BB add-in (the icon in an upper corner of the posted snippets will copy the contents to your clipboard, then go to a blank sheet, select the same cell that appears in the upper left of the worksheet snippet, and paste...that will copy the data and formulas that are posted in the snippet (for M code, the code will be on your clipboard awaiting pasting into the Power Query editor)). Here are the two tables mentioned, Table1 on the left and Table2 on the right (you may have to make these official Excel tables--I don't think XL2BB will do that automatically). To change a cell range into a table, click anywhere in the range, then Ctrl-t and a pop-up window will show the automatically detected table range. Confirm that is correct, and also confirm that the table uses headers. Then to change the table name, click anywhere in the table, go to the top-level Table menu, and you should see a Table Name: area that can be edited. Both of these tables reside on a worksheet named Pestomania in my workbook.

MrExcel_20230929_Pestomania.xlsx

A

B

C

D

E

F

G

H

I

J

1

Program

Customer Name

Year

Quantity

Unit Price

Under Contract

Program

Lot Size

Item Code

2

Program 1

Customer 1

2024

1

140

Yes

Program 1

1

123

3

Program 1

Customer 1

2025

1

800

No

Program 2

6

456

4

Program 1

Customer 1

2026

1

1000

No

Program 3

2

789

5

Program 1

Customer 1

2027

1

1000

No

6

Program 1

Customer 1

2028

1

1000

No

7

Program 2

Customer 2

2024

30

640

No

8

Program 2

Customer 2

2025

30

640

No

9

Program 2

Customer 2

2026

30

640

No

10

Program 2

Customer 2

2027

30

640

No

11

Program 2

Customer 2

2028

30

640

No

12

Program 3

Customer 1

2024

24

379

Yes

13

Program 3

Customer 1

2025

32

288

Yes

14

Program 3

Customer 1

2026

40

293

No

15

Program 3

Customer 1

2027

40

330

No

16

Program 3

Customer 1

2028

40

330

No

Pestomania

On a separate worksheet called Holidays, I have the following table named tblHolidays

MrExcel_20230929_Pestomania.xlsx

A

B

1

Holiday

Celebrated On

2

New Year's Day

January 1st

3

Birthday of Martin Luther King, Jr.

Third Monday of January

4

Washington's Birthday

Third Monday of February

5

Memorial Day

Last Monday of May

6

Juneteenth Independence Day

June 19th

7

Independence Day

July 4th

8

Labor Day

First Monday of September

9

Columbus Day

Second Monday of October

10

Veterans Day

November 11th

11

Thanksgiving Day

Fourth Thursday of November

12

Christmas Day

December 25th

Holidays

Then I have a third worksheet called Results Table that looks like this (small sample below)...this is where Power Query returns the results:



Program	Customer Name	Delivery Date	Item Code	Lot Size	Unit Price	Year	Quantity
Program 1	Customer 1	1/2/2025	123	1	800	2025	1
Program 1	Customer 1	1/2/2026	123	1	1000	2026	1
Program 1	Customer 1	1/4/2027	123	1	1000	2027	1
Program 1	Customer 1	1/3/2028	123	1	1000	2028	1
Program 2	Customer 2	1/2/2024	456	6	640	2024	30
Program 2	Customer 2	4/1/2024	456	6	640	2024	30
Program 2	Customer 2	7/1/2024	456	6	640	2024	30
Program 2	Customer 2	9/30/2024	456	6	640	2024	30

The trickiest part is getting the various components working in Power Query. There are seven items that need to be set up. In your workbook, click on the upper Data menu and choose the Get Data drop down button under the Get & Transform Data submenu. Then choose the option to Launch Power Query Editor... This opens the Power Query editor. On the left side of the split window will be a list of queries and functions (nothing will appear there yet). (***) Right click on an empty space in the left window and choose New Query > Other Sources > Blank Query. This will create a blank query. Click on that query and choose View > Advanced Editor, which will open the code editor. Delete everything in there and paste the code for item 1 below...then click on Done. Now right click on the query name and Rename it to match the names described below (e.g., the 1st query below is named Table1). Do the same steps beginning where I've inserted (***) above to establish all seven queries/functions. The final steps are to close out Power Query (File > Close & Load). Then go back into Excel, click on Date > Queries & Connections and you should see all of the queries/functions just added. Right click on those that are to be loaded as "Connection only" and change how they load by choosing "Load to..." and change the option to Connection Only. This means that the query will produce a virtual table---something that can be used by other queries, but it won't load as a physical table to your workbook. If, when first setting these queries up, physical output tables are created from some of these queries, simply delete those sheets.

If I haven't missed anything in the description, you should be able to adapt this to your needs. To refresh the results table anytime you make changes, you'll use Data > Refresh All to rebuild the results table.

1. A query called Table1 that is configured to load as "Connection only". The code...

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Program", type text}, {"Customer Name", type text}, {"Year", Int64.Type}, {"Quantity", Int64.Type}, {"Unit Price", Int64.Type}, {"Under Contract", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Under Contract] = "No"))
in
    #"Filtered Rows"

2. A query called Table2 that is configured to load as "Connection only". The code...

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Program", type text}, {"Lot Size", Int64.Type}, {"Item Code", Int64.Type}})
in
    #"Changed Type"

3. A query called Holidays that is configured to load as "Connection only". The code...

Power Query:

let
    Source = Excel.CurrentWorkbook(){[Name="tblHolidays"]}[Content],
    ExtractMonth = Table.AddColumn(Source, "Month", each let splitCelebrated2 = List.Reverse(Splitter.SplitTextByDelimiter(" of ", QuoteStyle.None)([Celebrated On])), splitsplitCelebrated20 = Splitter.SplitTextByDelimiter(" ", QuoteStyle.None)(splitCelebrated2{0}?) in splitsplitCelebrated20{0}?, type text),
    DayPart = Table.AddColumn(ExtractMonth, "DayCel", each Splitter.SplitTextByDelimiter(" of ", QuoteStyle.None)([Celebrated On]){0}, type text),
    DayNumber = Table.AddColumn(DayPart, "DayNumber", each Text.Select([Celebrated On], {"0".."9"})),
    ExtractDay = Table.AddColumn(DayNumber, "Day", each if [DayNumber]="" then [DayCel] else [DayNumber]),
    #"Removed Columns" = Table.RemoveColumns(ExtractDay,{"Celebrated On", "DayCel", "DayNumber"})
in
    #"Removed Columns"

4. A query called HolidaysList that is configured to load as "Connection only". The code...

Power Query:

let
    Source = Table1,
    #"Removed Other Columns1" = Table.SelectColumns(Source,{"Year"}),
    #"Added Custom" = Table.AddColumn(#"Removed Other Columns1", "Custom", each Holidays),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Holiday", "Month", "Day"}, {"Holiday", "Month", "Day"}),
    #"Added Month#" = Table.AddColumn(#"Expanded Custom", "Month#", each if [Month] = "January" then 1 else if [Month] = "February" then 2 else if [Month] = "March" then 3 else if [Month] = "April" then 4 else if [Month] = "May" then 5 else if [Month] = "June" then 6 else if [Month] = "July" then 7 else if [Month] = "August" then 8 else if [Month] = "September" then 9 else if [Month] = "October" then 10 else if [Month] = "November" then 11 else if [Month] = "December" then 12 else null),
    #"Changed Month# to Integer" = Table.TransformColumnTypes(#"Added Month#",{{"Month#", Int64.Type}}),
    #"Get Date1" = Table.AddColumn(#"Changed Month# to Integer", "GetDate1", each if Text.Length([Day]) < 3 then #date([Year],[#"Month#"],Number.FromText([Day])) else #date([Year],[#"Month#"],1)),
    #"Duplicated Column" = Table.DuplicateColumn(#"Get Date1", "Day", "Day - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Day - Copy", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Day Position", "Day Name"}),
    #"Added Day Position" = Table.AddColumn(#"Split Column by Delimiter", "Position", each if [Day Position] = "First" then 1 else if [Day Position] = "Second" then 2 else if [Day Position] = "Third" then 3 else if [Day Position] = "Fourth" then 4 else if [Day Position] = "Fifth" then 5 else null),
    #"Added Day Number" = Table.AddColumn(#"Added Day Position", "DayNumber", each if [Day Name] = "Sunday" then 1 else if [Day Name] = "Monday" then 2 else if [Day Name] = "Tuesday" then 3 else if [Day Name] = "Wednesday" then 4 else if [Day Name] = "Thursday" then 5 else if [Day Name] = "Friday" then 6 else if [Day Name] = "Saturday" then 7 else null),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Day Number",{{"Position", Int64.Type}, {"DayNumber", Int64.Type}, {"GetDate1", type date}}),
    #"Get Date2" = Table.AddColumn(#"Changed Type2", "GetDate2", each if [Day Position] = "Last" then null else if Text.Length([Day]) > 2 then Date.AddDays([GetDate1],[Position]*7-1 - Date.DayOfWeek(Date.AddDays([GetDate1],7-[DayNumber]))) else null),
    #"Get Date3" = Table.AddColumn(#"Get Date2", "GetDate3", each if [Day Position] = "Last" then Date.AddDays(Date.EndOfMonth([GetDate1]),0-Date.DayOfWeek(Date.AddDays(Date.EndOfMonth([GetDate1]), 1-[DayNumber]))) else null),
    #"Changed Type3" = Table.TransformColumnTypes(#"Get Date3",{{"GetDate2", type date}, {"GetDate3", type date}}),
    #"Holiday Date" = Table.AddColumn(#"Changed Type3", "Holiday Date", each if Text.Length([Day]) < 3 then [GetDate1] else if [Day Position] <> "Last" then [GetDate2] else if [Day Position] = "Last" then [GetDate3] else null),
    #"Celebrated On" = Table.AddColumn(#"Holiday Date", "Celebrated On", each if Date.DayOfWeekName([Holiday Date]) = "Saturday" then Date.AddDays([Holiday Date], -1) else if Date.DayOfWeekName([Holiday Date]) = "Sunday" then Date.AddDays([Holiday Date], 1) else [Holiday Date] ),
    #"Changed Type1" = Table.TransformColumnTypes(#"Celebrated On",{{"Holiday Date", type date}, {"Celebrated On", type date}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type1",{"Year", "Holiday", "Month", "Day", "Holiday Date", "Celebrated On"})
in
    #"Removed Other Columns"

5. A query called SummaryTable. These results are loaded as a table on the Results Table sheet. The code...

Power Query:

let
    Source = Table.NestedJoin(Table1, {"Program"}, Table2, {"Program"}, "Table2", JoinKind.LeftOuter),
    CombinedTables = Table.ExpandTableColumn(Source, "Table2", {"Lot Size", "Item Code"}, {"Lot Size", "Item Code"}),
    #"Added Custom" = Table.AddColumn(CombinedTables, "Delivery Date", each DeliveryDates([Year], [Quantity], [Lot Size], HolidaysList[Celebrated On])),
    #"Expanded Delivery Date" = Table.ExpandListColumn(#"Added Custom", "Delivery Date"),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded Delivery Date",{"Program", "Customer Name", "Delivery Date", "Item Code", "Lot Size", "Unit Price", "Year", "Quantity", "Under Contract"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Reordered Columns",{{"Delivery Date", type date}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Under Contract"})
in
    #"Removed Columns"

6. A function called Networkdays2. The code...

Power Query:

(StartDate, EndDate, optional Weekend, optional Holidays)=> 
let
    //if a date is blank, make it equal to 30-Dec-1899
    FromDate = if StartDate = null then #date(1899,12,30) else StartDate,
    ToDate = if EndDate = null then #date(1899,12,30) else EndDate,

    //StartDate should be less than or equal to EndDate. If it is more than EndDate, then we will multiply the final result with negative sign. Let's store this sign
    Sign = if ToDate<FromDate then -1 else 1,
    Start_Date = List.Min({FromDate, ToDate}),
    End_Date = List.Max({FromDate, ToDate}),

    //Support no Holidays option
    Holidays = if Holidays = null then {} else Holidays,
    
    //Prepare a list of dates from Start date to End date and remove Holidays from there
    ListOfAllDates = List.Difference(List.Dates(Start_Date,Duration.Days(End_Date-Start_Date)+1,#duration(1,0,0,0)),Holidays),
    
    //Adjust Weekend Parameter
    SetWeekend = if Weekend ="" or Weekend = null then "1" else Text.From(Weekend),
    //options 1 to 7 and 11 to 17 are parameters for weekends, with strings beginning on Monday. For details, see...
    // https://support.microsoft.com/en-us/office/networkdays-intl-function-a9b26239-4f20-46a1-9ab8-4e925bfd5e28
    WeekendParamList = {"1".."7"} & {"11","12","13","14","15","16","17"},
    WeekendLookupList = {"0000011","1000001","1100000","0110000","0011000","0001100","0000110","0000001","1000000","0100000","0010000","0001000","0000100","0000010"},
    WeekendString = if Text.Length(SetWeekend)=7 then SetWeekend else WeekendLookupList{List.PositionOf(WeekendParamList,SetWeekend)},

    //Generate a list which has the position of weekends. Hence, for 1000011, will generate {1,6,7}
    WeekendList = List.RemoveMatchingItems(List.Transform(List.Positions(Text.ToList(WeekendString)), (i)=>(i+1)*Number.From(Text.ToList(WeekendString){i})),{0}),
    
    //Remove the dates that match the weekend criteria and take the count multiplied by Sign (if total count desired)
    //Networkdays = Sign * List.Count(List.Select(ListOfAllDates,(i)=>not List.Contains(WeekendList,Date.DayOfWeek(i,1)+1)))
    //Remove the dates that match the weekend criteria
    Networkdays = List.Select(ListOfAllDates,(i)=>not List.Contains(WeekendList,Date.DayOfWeek(i,1)+1))
in
    Networkdays

7. A function called DeliveryDates. The code...

Power Query:

(Year, Quantity, LotSize, optional Holidays)=> 
let
    // support no Holidays option
    Holidays = if Holidays = null then {} else Holidays,
    
    // number of work days in calendar year, accounting for weekends, holidays, and shifted holiday celebration dates
    WorkDaysList = Networkdays2(#date(Year,1,1),#date(Year,12,31),1,Holidays),
    NumberWorkDays = List.Count(WorkDaysList),

    // number of deliveries in year based on total quantity and lot size of each delivery, assumes MOD(Quantity,LotSize)=0
    NumberDeliveries = Quantity/LotSize,

    // interval in days (not necessarily an integer) between equally spaced deliveries, beginning 1st work day of year
    IntervalDays = if NumberDeliveries = 1 then NumberWorkDays-1 else (NumberWorkDays-1)/(NumberDeliveries-1),
    
    // construct work day mapping
    DeliveryDaysList = List.Transform(List.Numbers(0,NumberDeliveries,IntervalDays), each Number.Round(_,0)),

    // extract delivery dates from WorkDaysList
    DeliveryDates = List.Transform(DeliveryDaysList, each WorkDaysList{ _ })
in
    DeliveryDates

Create Equally Distributed Deliverables Table

Pestomania

Active Member

Excel Facts

KRice

Well-known Member

Dropbox

Pestomania

Active Member

Dropbox

Pestomania

Active Member

Dropbox

KRice

Well-known Member

Dropbox

Similar threads

Forum statistics

Share this page

Create Equally Distributed Deliverables Table

Active Member

Excel Facts

Well-known Member

Active Member

Active Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock