Query to segregate information

You really can’t split the data into months with the information you have provided in your Data Set above.

First, your last three columns do not seem to derive from anything you have provided, nor are they useful in determining the missing information.

You need to provide:

• A holiday list for the relevant country
• A weekend list for the relevant country and employee
• An employee ID if you want to aggregate by employee.
• Employment hours per day

In the example below, I “Assumed” USA for the country with USA Holiday dates and Saturday/Sunday weekends. The Holiday dates are stored in a separate Excel Workbook, and the weekend dates are hard-coded (you could use various methods to pull this out for varying countries. If you only have a few, you could use “if .. then .. else if … statements”. If many, then a lookup table).

I also output the data as “Days” since you don’t provide the number of workhours per day. You can edit the code once you figure out the hrs/workday (which may vary depending on Employee and Country).

In any event, given your initial data plus the extra data you showed in a follow up question:

let
   
//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Vacation"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"startdate", type date}, {"enddate", type date}, {"quantityinhours", Int64.Type}, 
        {"quantityindays", Int64.Type}, {"workingdaysperweek", Int64.Type}}),

    #"Remove Useless Columns" = Table.RemoveColumns(#"Changed Type",{"quantityinhours", "quantityindays", "workingdaysperweek"}),

//Use USA Holidays and Weekends for this example
    Holidays = Excel.Workbook(File.Contents("C:\Users\ron\OneDrive\Holidays.xlsx"), null, true)
                    {[Item="USA",Kind="Table"]}[Data][USA],
    Weekends = {Day.Saturday,Day.Sunday},

//Vacation Day List with Holidays and Weekends removed
    #"Vacation Day List" = Table.AddColumn(#"Remove Useless Columns","VacList", 
       each
            List.RemoveMatchingItems( 
                List.Select(
                    List.Dates(
                        [startdate],
                        Duration.Days([enddate]-[startdate])+1,
                        #duration(1,0,0,0)), 
                    (d)=> not List.Contains(Weekends,Date.DayOfWeek(d))),
                Holidays), 
            type list),

    #"Work Days on Vacation" = Table.AddColumn(#"Vacation Day List","Total Work Days", each List.Count([VacList]), Int64.Type),
    #"Aggregate By Month" = Table.AddColumn(#"Work Days on Vacation", "by Month", (v)=>
        let 
            VacTbl = Table.FromColumns({v[VacList]}, type table[Dates=date]),
            #"Group by Month" = Table.Group(VacTbl,{"Dates"},{
                {"Months", each _}
                }, GroupKind.Local, (x,y)=>Number.From(Date.Month(x[Dates]) <> Date.Month(y[Dates]))),
            #"Remove Dates Column" = Table.RemoveColumns(#"Group by Month",{"Dates"}),
            #"Month Count" = List.Accumulate(
                #"Remove Dates Column"[Months],
                [],
                (s,c)=> s & Record.FromList({Table.RowCount(c)}, {
                    Date.ToText(#date(Date.Year(c[Dates]{0}),Date.Month(c[Dates]{0}),1), "yyyy-MM")}     
                )
            )
        in 
            #"Month Count", type record),
    #"Removed Columns" = Table.RemoveColumns(#"Aggregate By Month",{"VacList"}),

//List of Included Months for output
    #"All Months" = 
        List.Sort(
            List.Distinct(
                List.Combine(
                    List.Transform(
                        #"Removed Columns"[by Month], 
                        each Record.FieldNames(_))))),

//Expand the aggregated tables
    #"Expanded by Month" = Table.ExpandRecordColumn(#"Removed Columns", "by Month", #"All Months")
in
    #"Expanded by Month"

Results