Forum Discussion
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 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.
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
8 Replies
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?
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.
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
