Create Weeks: Generating rows in a table for each week within a start and end date

[lelandm]

This may be a complicated question, but here we go:

I currently have a table where each entry has a number, a start date and an end date.

What I am looking to do is either add rows to that table, or generate a new table that has a row for each week between the start and end date.

Example:

Name      | Hours   | Start Date | End Date    |

John Doe |   45      | 1/1/2017   | 1/14/2017  |

It would take the table above and turn it into the table below:

Name       | Hours   | Start Date |    End Date   |

John Doe |   45       | 1/1/2017   |  1/7/2017     |

John Doe |   45       | 1/8/2017   |  1/14/2017   |

Does anyone know of a way to do this within Power BI?

Thank you.

[MarcelBeug]

More specs please.

Does any week has to start with a Sunday?

If your start is on another day, do you need the Sunday before or after?

Or does the first week has to start with the original start date, and end on a Saturday?

Does any week has to end with a Saturday?

If your end is on another day, do you need the Saturday before or after?

Or does the last week has to start with Sunday and end on the original end date?

Are you looking for a DAX or a Power Query solution?

[lelandm]

Every entry begins on a Monday and ends on a Friday, if the entry goes beyond one week it will begin on the first week's Monday and end on the final week's Friday. So every entry will have a duration of either 4, 11, 18... days

Either a DAX or PowerQuery solution is welcome.

[MarcelBeug · Accepted solution]

So your example data is not very representative, with starts on Sundays and ends on Saturdays?

Anyhow, this would be a Power Query solution:

let
    Source = Data,
    AddedNewStartAndEnd = 
        Table.AddColumn(
            Source, 
            "NewStartAndEnd", 
            (This) => 
                List.Transform(
                    {0..Number.RoundDown(
                        Duration.Days(
                            This[End]-This[Start])/7,
                        0)},
                    each [Start = This[Start] + #duration(_ * 7,0,0,0), End = Start + #duration(4,0,0,0)]),
            type {[Start = date, End = date]}),
    RemovedColumns = Table.RemoveColumns(AddedNewStartAndEnd,{"Start", "End"}),
    ExpandedNewStartandEndLists = Table.ExpandListColumn(RemovedColumns, "NewStartAndEnd"),
    ExpandedNewStartandEndRecords = Table.ExpandRecordColumn(ExpandedNewStartandEndLists, "NewStartAndEnd", {"Start", "End"}, {"Start", "End"})
in
    ExpandedNewStartandEndRecords

[lelandm]

Thank you very much for the solution. Unfortunately, I think I may need help implementing it.

Below is my first swing at adapting the code you provided. The data table I am adapting is named Assignments.

let
    Source = Assignments,
    AddedNewStartAndEnd =
        Table.AddColumn(
            Source,
            "NewStartAndEnd",
            (This) =>
                List.Transform(
                    {0..Number.RoundDown(
                        Duration.Days(
                            This [End Date]- This [Start Date])/7,
                        0)},
                    each [Start = This [Start Date] + #duration(_ * 7,0,0,0), End = Start + #duration(4,0,0,0)]),
            type {[Start = date, End = date]}),
    #"Expanded NewStartAndEnd" = Table.ExpandListColumn(AddedNewStartAndEnd, "NewStartAndEnd"),
    #"Expanded NewStartAndEnd1" = Table.ExpandRecordColumn(#"Expanded NewStartAndEnd", "NewStartAndEnd", {"Start", "End"}, {"New Start", "New End"})
in
    #"Expanded NewStartAndEnd1"

In the query editor everything looks good and the syntax checks out. However, when I attempt to close and apply the query it gives be the below error. Any thoughts?

Update: It appears the null values are due to freak occurances when end dates occuring before the start dates.

Help still needed to solve.

[lelandm]

Update: It appears the null values are due to end dates occuring before the start dates.

Help still needed to solve.

[MarcelBeug]

Then you can add a List.Max to step AddedNewStartAndEnd, to prevent values below 0:

    AddedNewStartAndEnd = 
        Table.AddColumn(
            Source, 
            "NewStartAndEnd", 
            (This) => 
                List.Transform(
                    {0..List.Max({0,Number.RoundDown(
                        Duration.Days(
                            This[End]-This[Start])/7,
                        0)})},
                    each [Start = This[Start] + #duration(_ * 7,0,0,0), End = Start + #duration(4,0,0,0)]),
            type {[Start = date, End = date]}),

[Zubair_Muhammad]

Hi @lelandm

Try this this DAX solution as well

Go to Modelling Tab>>>New Table

New Table =
GENERATE (
    TableName,
    GENERATESERIES (
        CALCULATE (
            VALUES ( TableName[Start Date] ),
            FILTER ( ALL ( TableName ), TableName[Name] = EARLIER ( TableName[Name] ) )
        ),
        CALCULATE (
            VALUES ( TableName[End Date] ),
            FILTER ( ALL ( TableName ), TableName[Name] = EARLIER ( TableName[Name] ) )
        ),
        7
    )
)

[Zubair_Muhammad]

@lelandm

See File attached here as well