Finding daily value based on work date between 2 dates in power query

[SCNCKS1]

Hi, I apologize in advance for the verbose question.

I have a table with Start_Date and End_Date and values for 4 services. I need to create a list to obtain daily values based on work dates to include holidays. 
I created a calculated table in powerbi that works perfectly, but i am not able to merge the information in power query with other existing tables so i am trying to create in power query. The code i used to create the calculated table (and the non-working day column in my powerbi date table) is below. I have created a list in power query of the dates between the start and stop date, but i need to remove the dates from the calculation that are not work dates (sat, sun, holiday) preferably built into the code, not having to create a separate holiday table.

__________________________________________

TBL_EST_FT_PRODUCTION =

SELECTCOLUMNS(

    GENERATE(

        ADDCOLUMNS(

            TBL_DA_Poly_Production_Rates,

                "Daily_OH_Est_Prod", DIVIDE(

                Tbl_Da_Poly_Production_rates[OHTotal],

                LOOKUPVALUE(

                    'Date'[Date],

                    'Date'[Date],

                    'TBL_DA_Poly_Production_Rates'[DA_EXPECTED_END_DATE],

                    'Date'[Non-Working Days],

                    BLANK()

                )

                    -RELATED('Date'[Date])

                ),

                "Daily_UG_Est_Prod", DIVIDE(

                TBL_DA_Poly_Production_Rates[UGTotal],

                LOOKUPVALUE(

                    'Date'[Date],

                    'Date'[Date],

                    'TBL_DA_Poly_Production_Rates'[DA_EXPECTED_END_DATE],

                    'Date'[Non-Working Days],

                    BLANK()

                )

                    -RELATED('Date'[Date])

                ),

                "Daily_FiberSpliced_Est_Prod", DIVIDE(

                TBL_DA_Poly_Production_Rates[FibersSplicedTotal],

                LOOKUPVALUE(

                    'Date'[Date],

                    'Date'[Date],

                    'TBL_DA_Poly_Production_Rates'[DA_EXPECTED_END_DATE],

                    'Date'[Non-Working Days],

                    BLANK()

                )

                    -RELATED('Date'[Date])

                ),

                "Daily_Units_Est_Prod", DIVIDE(

                TBL_DA_Poly_Production_Rates[UnitTotal],

                LOOKUPVALUE(

                    'Date'[Date],

                    'Date'[Date],

                    'TBL_DA_Poly_Production_Rates'[DA_EXPECTED_END_DATE],

                    'Date'[Non-Working Days],

                    BLANK()

                )

                    -RELATED('Date'[Date])

                )

        ),

    FILTER(

        ALLNOBLANKROW('Date'),

        AND(

            'Date'[Date] >= TBL_DA_Poly_Production_Rates[DA_EXPECTED_START_DATE],

            'Date'[Date] < TBL_DA_Poly_Production_Rates[DA_EXPECTED_END_DATE]

        )

    )

),

    "EST_PRODUCTION_Date", 'Date'[Date],

    "DA_Poly_ID",TBL_DA_Poly_Production_Rates[DAPolyID],

    "Production_Unit_ID",TBL_DA_Poly_Production_Rates[ProductionUnitID],

    "Production_Unit",TBL_DA_Poly_Production_Rates[ProductionUnit],

    "DA_OH_EST_PROD", [Daily_OH_Est_Prod],

    "DA_UG_EST_PROD", [Daily_UG_Est_Prod],

    "DA_UNITS_EST_PROD", [Daily_Units_Est_Prod],

    "DA_FIBERSSPLICED_EST_PROD", [Daily_FiberSpliced_Est_Prod]

)

_________________________________

Non-Working Days =

    SWITCH(

        TRUE(),

    -- January - Saturdays, non-working days    

    //// Non Working Day in January

    MONTH([Date]) = 1 && WEEKDAY([Date], 1 ) = 7, "Non-Working Day",

    -- February- Saturdays, non-working days    

         //// Non Working Day in February

        MONTH([Date]) = 2 && WEEKDAY([Date], 1 ) = 7, "Non-Working Day",

    -- March- Saturdays, non-working days    

         //// Non Working Day in March

        MONTH([Date]) = 3 && WEEKDAY([Date], 1 ) = 7, "Non-Working Day",

    -- Sundays

         //// Sunday

        WEEKDAY([Date], 1 ) = 1, "Non-Working Day",

  -- Thanksgiving

        MONTH([Date]) = 11 && DAY([Date]) IN { 22, 23, 24, 25, 26, 27, 28 } && WEEKDAY([Date], 1 ) = 5, "Thanksgiving",

        -- Friday after Thanksgiving

        MONTH([Date]) = 11 && DAY([Date]) IN { 23, 24, 25, 26, 27, 28, 29 } && WEEKDAY([Date], 1 ) = 6, "Friday after Thanksgiving",

    -- Christmas Eve (including weekend observations)

        //// When falling on Saturday, move to preceding Friday

        MONTH([Date]) = 12 && DAY([Date]) = 23 && WEEKDAY([Date], 1 ) = 6, "Christmas Eve (Observed)",

        //// When falling on Friday, move to preceding Thursday

        MONTH([Date]) = 12 && DAY([Date]) = 23 && WEEKDAY([Date], 1 ) = 5, "Christmas Eve (Observed)",

        //// When falling on Sunday, move to following Monday

        MONTH([Date]) = 12 && DAY([Date]) = 25 && WEEKDAY([Date], 1 ) = 2, "Christmas Eve (Observed)",

        //// When falling on normal workday (except Friday), do not shift holiday observation

        MONTH([Date]) = 12 && DAY([Date]) = 24 && WEEKDAY([Date], 1 ) IN { 2, 3, 4, 5 }, "Christmas Eve",

     -- Christmas Day (including weekend observations)

        //// When falling on Saturday, move to preceding Friday

        MONTH([Date]) = 12 && DAY([Date]) = 24 && WEEKDAY([Date], 1 ) = 6, "Christmas Day (Observed)",

        //// When falling on Sunday, move to following Monday

        MONTH([Date]) = 12 && DAY([Date]) = 26 && WEEKDAY( [Date], 1 ) = 2, "Christmas Day (Observed)",

        //// When falling on Monday, move to following Tuesday

        MONTH([Date]) = 12 && DAY([Date]) = 26 && WEEKDAY([Date], 1 ) = 3, "Christmas Day (Observed)",

        //// When falling on normal workday (except Monday), do not shift holiday observation

        MONTH([Date]) = 12 && DAY([Date]) = 25 && WEEKDAY([Date], 1 ) IN { 3, 4, 5, 6 }, "Christmas Day",

        -- Leave working days blank

        BLANK()

)

[SCNCKS1 · Accepted solution]

my solution, but if anything can improve, please let me know:

I created a networkdays function then created a list to expand the dates, it covers the weekends, but not the holidays. I used the same function to determine the network days to determine the daily values -->

______________________________________
Function -->

= (StartDate as date, EndDate as date) as number =>

let
DateList = List.Dates(StartDate, Number.From(EndDate - StartDate), #duration(1,0,0,0)),
RemoveWeekends = List.Select(DateList, each Date.DayOfWeek(_, Day.Monday) < 5),
CountDays = List.Count(RemoveWeekends)

in
CountDays

_________________________________

= Table.AddColumn(#"Changed Type1", "Dates_Test", each let
AllDates = {Number.From([DA_EXPECTED_START_DATE])..Number.From([DA_EXPECTED_END_DATE])},

StartDate =
List.Transform(AllDates, each Date.From(_)) in StartDate)