Splitting rows containing a date interval into as many rows as the nr of weeks of the given interval

[FilippoBelva]

Hi community, I am a biginner with Power Query and cannot resolve this problem:

This is my starting point, where a single row contains: start date, end date, total workload, number of weeks, 

Task	Start Date	End Date	Department	Employee	WorkLoad	Number of Weeks	Solar Days	Weekly Mean Workload
Task_01	08/01/2023	31/03/2023	ITF	FB	100	12	82	8
Task_02	15/01/2023	03/02/2023	IE	MS	30	3	19	10

My desired output is a table where, for each original record, the number of rows are as many as the number of weeks:

Task	Start Date	End Date	Department	Employee	WorkLoad	Number of Weeks	Solar Days	Weekly Mean Workload
Task_01	08/01/2023	31/03/2023	ITF	FB	100	12	82	8
Task_01	08/01/2023	15/01/2023	ITF	FB	100	1	7	8	added rows
Task_01	16/01/2023	23/01/2023	ITF	FB	100	1	7	8
Task_01	24/01/2023	31/01/2023	ITF	FB	100	1	7	8
Task_01	01/02/2023	08/02/2023	ITF	FB	100	1	7	8
Task_01	09/02/2023	16/02/2023	ITF	FB	100	1	7	8
Task_01	17/02/2023	24/02/2023	ITF	FB	100	1	7	8
Task_01	25/02/2023	04/03/2023	ITF	FB	100	1	7	8
Task_01	05/03/2023	12/03/2023	ITF	FB	100	1	7	8
Task_01	13/03/2023	20/03/2023	ITF	FB	100	1	7	8
Task_01	21/03/2023	28/03/2023	ITF	FB	100	1	7	8
Task_01	29/03/2023	05/04/2023	ITF	FB	100	1	7	8
Task_01	06/04/2023	13/04/2023	ITF	FB	100	1	7	8
Task_01	14/04/2023	21/04/2023	ITF	FB	100	1	7	8
Task_02	15/01/2023	03/02/2023	IE	MS	30	3	19	10
Task_02	15/01/2023	22/01/2023	IE	MS	30	1	7	10	added rows
Task_02	23/01/2023	30/01/2023	IE	MS	30	1	7	10
Task_02	31/01/2023	07/02/2023	IE	MS	30	1	7	10

[ronrsnfld · Accepted solution]

You can Add a Custom Column with the formula:

{0..[Number of Weeks]}

Then merely expand that List to new rows, and delete the added column

let

//Change next line to reflect actual data source
    Source = Excel.CurrentWorkbook(){[Name="Table13"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Task", type text}, {"Start Date", type date}, {"End Date", type date}, {"Department", type text}, 
        {"Employee", type text}, {"WorkLoad", Int64.Type}, {"Number of Weeks", Int64.Type}, 
        {"Solar Days", Int64.Type}, {"Weekly Mean Workload", Int64.Type}}),

//Add custom column with list of {0..number of weeks}
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each {0..[Number of Weeks]}, type list),

    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom",{"Custom"})
in
    #"Removed Columns"

[ImkeF]

Hello @FilippoBelva ,

please check out the enclosed Excel file.
It uses a function that I've published a while ago here: Date.DatesBetween to retrieve dates between 2 dates in Power BI and Power Query – The BIccountant

let 
// ----------------------- Documentation ----------------------- 
documentation_ = [
Documentation.Name =  " Dates.ListDateIntervals
", Documentation.Description = " Creates a list of dates according to the chosen interval between Start and End. Allowed values for 3rd parameter: ""Year"", ""Quarter"", ""Month"", ""Week"" or ""Day"".
" , Documentation.LongDescription = " Creates a list of dates according to the chosen interval between Start and End. The dates created will always be at the end of the interval, so could be in the future if today is chosen.
", Documentation.Category = " Table
", Documentation.Source = " http://www.thebiccountant.com/2017/12/11/date-datesbetween-retrieve-dates-between-2-dates-power-bi-power-query/ . 
", Documentation.Author = " Imke Feldmann: www.TheBIccountant.com . 
", Documentation.Examples = {[Description =  " see http://www.thebiccountant.com/2017/12/11/date-datesbetween-retrieve-dates-between-2-dates-power-bi-power-query/ .
" , Code = " 
 ", Result = " 
"]}],

// ----------------------- Function Code ----------------------- 
function_ =  
(From as date, To as date, optional Selection as text ) =>
let
// Create default-value "Day" if no selection for the 3rd parameter has been made
TimeInterval = if Selection = null then "Day" else Selection,

// Table with different values for each case
CaseFunctions = #table({"Case", "LastDateInTI", "TypeOfAddedTI", "NumberOfAddedTIs"},
		{   {"Day", Date.From, Date.AddDays, Number.From(To-From)+1},
		    {"Week", Date.EndOfWeek, Date.AddWeeks, Number.RoundUp((Number.From(To-From)+1)/7)},
		    {"Month", Date.EndOfMonth, Date.AddMonths, (Date.Year(To)*12+Date.Month(To))-(Date.Year(From)*12+Date.Month(From))+1},
		    {"Quarter", Date.EndOfQuarter, Date.AddQuarters, (Date.Year(To)*4+Date.QuarterOfYear(To))-(Date.Year(From)*4+Date.QuarterOfYear(From))+1},
		    {"Year", Date.EndOfYear, Date.AddYears,Date.Year(To)-Date.Year(From)+1} 
		} ),

// Filter table on selected case
		Case = CaseFunctions{[Case = TimeInterval]},
    
// Create list with dates: List with number of date intervals -> Add number of intervals to From-parameter -> shift dates at the end of each respective interval	
	DateFunction = List.Transform({0..Case[NumberOfAddedTIs]-1}, each Function.Invoke(Case[LastDateInTI], {Function.Invoke(Case[TypeOfAddedTI], {From, _})}))
in
    DateFunction,
// ----------------------- New Function Type ----------------------- 
type_ = type function (
      From as (type date),
      To as (type date),
      optional Selection as (type text meta [
                            Documentation.FieldCaption = "Select Date Interval",
                            Documentation.FieldDescription = "Select Date Interval, if nothing selected, the default value will be ""Day""",
                            Documentation.AllowedValues = {"Day", "Week", "Month", "Quarter", "Year"}
                            ])
        )
    as table meta documentation_,

// Replace the extisting type of the function with the individually defined
Result =  Value.ReplaceType(function_, type_)
 in 
Result