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
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
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.
Update: It appears the null values are due to end dates occuring before the start dates.
Help still needed to solve.
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]}),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 ) )
