Forum Discussion

lelandm's avatar
lelandm
Regular Visitor
8 years ago
Solved

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

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 ta...
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    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
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

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
    )
)