How do I split the source data week ranges into days according to the date range and hours per day?

[Anonymous]

I have this source data, the start date is always a Monday and the end date is a Sunday. The columns Mon to Sun represent scheduled hours per day for each week in the start and end range. Note that the range can be 1 week to 300 weeks.

1. Source Data
Schedule	Start	End	Mon	Tue	Wed	Thu	Fri	Sat	Sun
Schedule1	11/21/2022	11/27/2022	8	8	8	8	8	0	0
Schedule2	12/5/2022	12/25/2023	7	7	7	7	7	0	0
Schedule3	1/2/2023	1/15/2023	3.5	0	3.5	3.5	0	0	3.5

I would like to split the columns into rows by day so it becomes:

2. Desired Result
Schedule	Start	End	Hours
Schedule1	11/21/2022	11/21/2022	8
Schedule1	11/22/2022	11/22/2022	8
Schedule1	11/23/2022	11/23/2022	8
Schedule1	11/24/2022	11/24/2022	8
Schedule1	11/25/2022	11/25/2022	8
Schedule1	11/26/2022	11/26/2022	0
Schedule1	11/27/2022	11/27/2022	0
Schedule2	12/5/2022	12/5/2022	7
Schedule2	12/6/2022	12/6/2022	7
Schedule2	12/7/2022	12/7/2022	7
Schedule2	12/8/2022	12/8/2022	7
Schedule2	12/9/2022	12/9/2022	7
Schedule2	12/10/2022	12/10/2022	0
Schedule2	12/11/2022	12/11/2022	0
Schedule2	12/12/2022	12/12/2022	7
Schedule2	12/13/2022	12/13/2022	7
Schedule2	12/14/2022	12/14/2022	7
Schedule2	12/15/2022	12/15/2022	7
Schedule2	12/16/2022	12/16/2022	7
Schedule2	12/17/2022	12/17/2022	0
Schedule2	12/18/2022	12/18/2022	0
Schedule2	12/19/2022	12/19/2022	7
Schedule2	12/20/2022	12/20/2022	7
Schedule2	12/21/2022	12/21/2022	7
Schedule2	12/22/2022	12/22/2022	7
Schedule2	12/23/2022	12/23/2022	7
Schedule2	12/24/2022	12/24/2022	0
Schedule2	12/25/2022	12/25/2022	0
Schedule3	1/2/2023	1/2/2023	3.5
Schedule3	1/3/2023	1/3/2023	0
Schedule3	1/4/2023	1/4/2023	3.5
Schedule3	1/5/2023	1/5/2023	3.5
Schedule3	1/6/2023	1/6/2023	0
Schedule3	1/7/2023	1/7/2023	0
Schedule3	1/8/2023	1/8/2023	3.5
Schedule3	1/9/2023	1/9/2023	3.5
Schedule3	1/10/2023	1/10/2023	0
Schedule3	1/11/2023	1/11/2023	3.5
Schedule3	1/12/2023	1/12/2023	3.5
Schedule3	1/13/2023	1/13/2023	0
Schedule3	1/14/2023	1/14/2023	0
Schedule3	1/15/2023	1/15/2023	3.5

Ultimately I'm trying to create a planner that can be viewed by day(s) for any specified date range. I guess the desired result is what I think I need to achieve this.

How can I do this by either using Power Query or DAX? Thanks in advance.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please refer to my steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7OSE0pzUk1VNJRMjTUNwIiAyMjKMccxrHAgg3AOFYHYQZYm5G+KdwII30jOMccC8Y0whikTd8IpAnCNDSFsY31TKFaICwEHyYWGwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Schedule = _t, Start = _t, End = _t, Mon = _t, Tue = _t, Wed = _t, Thu = _t, Fri = _t, Sat = _t, Sun = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Schedule", type text}, {"Start", type date}, {"End", type date}, {"Mon", type number}, {"Tue", Int64.Type}, {"Wed", type number}, {"Thu", type number}, {"Fri", Int64.Type}, {"Sat", Int64.Type}, {"Sun", type number}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"Start", Int64.Type}, {"End", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each {[Start]..[End]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Custom",{{"Start", type date}, {"End", type date}, {"Custom", type date}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type2",{"Schedule", "Start", "End", "Custom", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns",{"End"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "end"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Custom", each Date.DayOfWeek([end],Day.Sunday)),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom1",{"Schedule", "Start", "end", "Custom", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Reordered Columns1", {"Schedule", "Start", "end", "Custom"}, "Attribute", "Value"),
    #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Columns", "Custom.1", 
each if [Schedule] = "Schedule1" and List.Contains({1..5},[Custom]) then 8 
else if [Schedule] = "Schedule2" and List.Contains({1..5},[Custom]) then 7 
else if [Schedule] = "Schedule3" and List.Contains({0,1,3,4},[Custom]) then 3.5 
else 0),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column",{"Attribute", "Value", "Custom"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns1")
in
    #"Removed Duplicates"

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hi Polly, thank you so much, this is much smarter than the multi step workaround that I had come up with. Thanks again.

[speedramps]

Try this

Click here to download solution 

How it works ...

Remove the enddate column, because you dont need it and less in more.

Rename Mon, Tue, Wed , Thu, etc columns  to 1,2,3,4,5,6,7

Select the schedule and start column and unpivot the other columns

add a new customer column for the date = start + dayofweek

remove the uneeded columns, change the data types and column names and volia!  You now have fact table.

Now add a calandar table with contiguous dates and relate it to your fact table.

You need calandar table with contiguous  dates because you face table may have missing days.

Click here to learn about power bi calendar tables 

Thanks for reaching out for help.

I put in a lot of effort to help you, now please quickly help me by giving kudos.

Remember we are unpaid volunteers and here to coach you with Power BI and DAX skills and techniques, not do the users job for them. So please click the thumbs up and accept as solution button. 

If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime.  I prefer to teach members on this forum techniques rather give full solutions and do their job. You can then adapt the technique for your solution, learn some DAX skills for next time and soon become a Power BI Super User like me. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

[Anonymous]

Hi, I appreciate the quick response and the effort. However the end date is important because the schedules are not just a week in length; schedule 1 is one week, but schedule 2 is three weeks and schedule 3 is two weeks. The respective Mon to Sun hours split should then be duplicated in each of these weeks. Some of our project schedules are even longer. That's the bit that I'm really struggling with. Advice gratefully appreciated.