Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
FilippoBelva
Frequent Visitor

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

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, 

TaskStart DateEnd DateDepartmentEmployeeWorkLoadNumber of WeeksSolar DaysWeekly Mean Workload
Task_0108/01/202331/03/2023ITFFB100                            1282                                                     8
Task_0215/01/202303/02/2023IEMS30                              319                                                   10

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

TaskStart DateEnd DateDepartmentEmployeeWorkLoadNumber of WeeksSolar DaysWeekly Mean Workload 
Task_0108/01/202331/03/2023ITFFB100                            1282                      8 
Task_0108/01/202315/01/2023ITFFB100                              17                      8added rows
Task_0116/01/202323/01/2023ITFFB100                              17                      8
Task_0124/01/202331/01/2023ITFFB100                              17                      8
Task_0101/02/202308/02/2023ITFFB100                              17                      8
Task_0109/02/202316/02/2023ITFFB100                              17                      8
Task_0117/02/202324/02/2023ITFFB100                              17                      8
Task_0125/02/202304/03/2023ITFFB100                              17                      8
Task_0105/03/202312/03/2023ITFFB100                              17                      8
Task_0113/03/202320/03/2023ITFFB100                              17                      8
Task_0121/03/202328/03/2023ITFFB100                              17                      8
Task_0129/03/202305/04/2023ITFFB100                              17                      8
Task_0106/04/202313/04/2023ITFFB100                              17                      8
Task_0114/04/202321/04/2023ITFFB100                              17                      8
Task_0215/01/202303/02/2023IEMS3031910 
Task_0215/01/202322/01/2023IEMS301710added rows
Task_0223/01/202330/01/2023IEMS301710
Task_0231/01/202307/02/2023IEMS301710

 

1 ACCEPTED SOLUTION
ronrsnfld
Super User
Super User

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"

 

ronrsnfld_0-1672085156759.png

 

 

 

View solution in original post

2 REPLIES 2
ronrsnfld
Super User
Super User

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"

 

ronrsnfld_0-1672085156759.png

 

 

 

ImkeF
Super User
Super User

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

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors