Query to segregate information

[LucianaYumi]

Hi. 

I'm trying to breakdown some information: 

Below is the available data:

I need to segregate per month. Considering the last line:

The employee leave started: 22/09/23 until 01/10/23. I need to break the quantityindays column between these 2 months (september and october), considering number of working days per week. In the end, I need to split the information considering: how many hous per each month (important to consider the number of working hours per week (it can be different for each case). Thank you very much!!

[v-stephen-msft]

Hi @LucianaYumi ,

Sorry, not very clear. What does your expected reuslt look like?

If possible, please also provide the columns of the desired result in table form, along with some explanation of the calculation logic. Thanks.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[LucianaYumi]

Hello Stephen. 

Thank you for your reply. 

Below you can find an example:

This employee took a leave from 30/jan to 26/feb. 

I need to build a query to break his hours (216 hours total) per month. In this case, I need to consider he works 6 days per week. I need to know how many hours is part of January and how many hours is part of February. 

Thank you very much. 

Luciana

[ronrsnfld]

I think you also need to provide which days of the week are the working days, and which day of the week is the first day of the week. In your initial example, where workingdaysperweek = 5, it is not possible to replicate quantityofdays without that information.

[LucianaYumi]

Hi Ron. Thank you for your reply. 

Yes. I created a calendar excel table to support. The problem: as I'm working with different countries, weekdays are different (for Saudi Arabia, for example, sunday is working day). I also included the public holiday for each case. As a general rule, I'm planning to consider the number of working days same for all. Considering the country:

If UAE country and number of working days =5 -> monday to friday. Saturday and Sunday are weekends. 

If UAE country and number of working days =6 -> monday to Saturday. Sunday is weekend.

If Saudi Arabia and number of working days = 5 -> sunday to thursday. Friday and Saturday are weekend days. 

If Saudi Arabia and number of working days = 6 -> sunday to Friday and Saturday is weekend day.

I don't know if the calendar is a good option or I can include those rules in the query. Also, I don't have an specific format...can be each month in a different column. The easiest way will be great. 

Thank you! 

[ronrsnfld · Accepted solution]

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