Forum Discussion
Create Weeks: Generating rows in a table for each week within a start and end date
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
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 ExpandedNewStartandEndRecordsThank 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.
Update: It appears the null values are due to end dates occuring before the start dates.
Help still needed to solve.