How to: Expanding 1 Record w/StartDate and EndDate to many Records (one for each day between)

[Anonymous]

Hi there,

You all have been awesome!  Thank you so much for your help! 

Moving forward with my project, the next roadblock I have hit is around transforming the data I've received for our planning tool.  I'm getting a single row that I want to expand into individual dates.  I'm brand new to data manipulation in Power BI, any feedback would be appreciated!  @ImkeF , I've seen mention of you being a M wizard.  You were an awesome help last time, any ideas here?

Current Format

RecordID          StartDate          EndDate          DaysBetween      HoursPerDay
12345             1/1/2020           1/3/2020         3                8
12346             3/1/2020           1/4/2020         4                6

Desired Format

Date        RecordID        Hours
1/1/2020    12345           8
1/2/2020    12345           8
1/3/2020    12345           8
3/1/2020    12346           6
3/2/2020    12346           6
3/3/2020    12346           6
3/4/2020    12346           6

Bonus Points if it can set Sat/Sun to 0!  I imagine I can handle that by adding another column, but still working out my process there.

[Ashish_Mathur · Accepted solution]

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"RecordID", Int64.Type}, {"StartDate", type datetime}, {"EndDate", type datetime}, {"DaysBetween", Int64.Type}, {"HoursPerDay", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"DaysBetween"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Date", each {Number.From([StartDate])..Number.From([EndDate])}),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Date",{"StartDate", "EndDate"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Date", "RecordID", "HoursPerDay"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Date", type date}})
in
    #"Changed Type1"

Hope this helps.

[mahoneypat]

I was already working on this and noticed new post when I came back to post this.  Probably same approach, but in case not ...

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjFV0lEy1DfUNzIwMgAzjWFMYyC2UIrVgagzA4kgqzOBMU2A2EwpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [RecordID = _t, StartDate = _t, EndDate = _t, Days8etween = _t, HoursPerDay = _t]),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"StartDate", type date}, {"EndDate", type date}}, "en-150"),
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Days8etween", Int64.Type}, {"HoursPerDay", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "DateList", each List.Dates([StartDate],[Days8etween],#duration(1,0,0,0))),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"StartDate", "EndDate", "Days8etween"}),
#"Expanded DateList" = Table.ExpandListColumn(#"Removed Columns", "DateList"),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded DateList",{{"DateList", type date}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"DateList", "RecordID", "HoursPerDay"}),
#"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"DateList", "Date"}, {"HoursPerDay", "Hours"}})
in
#"Renamed Columns"

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

As always... this community rocks!

I did some minor tweaks, but wanted to attached my version for reference.  Specifically, I filtered out when the date range wsa not defined as well as 0 out weekends.  Thank you again for all the help!  This was a lifesaver!

let
    Source = #"Project Plan",
    #"FilteredSource" = Table.SelectRows(#"Source", each ([Business Days Per Period] > 0)),

    #"Changed Type" = Table.TransformColumnTypes(#"FilteredSource",{{"EmployeeID", type text}, {"Period Start Date", type datetime}, {"Period End Date", type datetime}, {"Business Days Per Period", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Business Days Per Period"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Date", each {Number.From([Period Start Date])..Number.From([Period End Date])}),
    #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Date",{"Period Start Date", "Period End Date"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"Date", "EmployeeID", "periodHrs"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Date", type date}}),
    //#"Reordered Columns1" = Table.ReorderColumns(#"Changed Type1",{"Date", "ProjectID", "PlanID", "WeekNbr", "HoursPerDay", "hardbooked", "assignmentID", "taskID", "periodCost", "periodBill", "costrate", "billingrate", "Project Start Date", "Project End Date", "Total Days Per Period", "EmployeeID", "PrjEmpID"}),
    #"Removed Columns2" = Table.RemoveColumns(#"Changed Type1",{"PlanID","WeekNbr"}),
    #"Reordered Columns2" = Table.ReorderColumns(#"Removed Columns2",{"Date", "ProjectID", "EmployeeID", "HoursPerDay",  "hardbooked", "assignmentID", "taskID", "periodCost", "periodBill", "costrate", "billingrate", "Project Start Date", "Project End Date", "Total Days Per Period", "PrjEmpID"}),
    #"Removed Columns3" = Table.RemoveColumns(#"Reordered Columns2",{"hardbooked", "assignmentID", "taskID", "periodCost", "periodBill", "costrate", "billingrate", "Project Start Date", "Project End Date", "Total Days Per Period", "PrjEmpID"}),
    #"Removed Columns4" = Table.RemoveColumns(#"Removed Columns3",{"periodHrs"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns4", "Day Of Week", each 
    
    if Date.DayOfWeek([Date])=0 then "Sun" else
    if Date.DayOfWeek([Date])=1 then "Mon" else
    if Date.DayOfWeek([Date])=2 then "Tue" else
    if Date.DayOfWeek([Date])=3 then "Wed" else
    if Date.DayOfWeek([Date])=4 then "Thur" else
    if Date.DayOfWeek([Date])=5 then "Fri" else
    if Date.DayOfWeek([Date])=6 then "Sat" else "???"),

    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Weekend?", each if Date.DayOfWeek([Date])=0 or Date.DayOfWeek([Date])=6 then "Y" else "N"),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "NewHours", each if [#"Weekend?"] = "Y" then 0 else [HoursPerDay]),
    #"Removed Columns0" = Table.RemoveColumns(#"Added Custom3",{"HoursPerDay"}),
    #"Changed Type1a" = Table.TransformColumnTypes(#"Removed Columns0",{{"EmployeeID", type text}}),
    #"Added Custom4" = Table.AddColumn(#"Changed Type1a", "WeekNbr", each Date.WeekOfYear([Date])),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom4",{{"WeekNbr", type text}}),
    #"Added Custom5" = Table.AddColumn(#"Changed Type2", "Month", each Date.MonthName([Date])),
    #"Reordered Columns0" = Table.ReorderColumns(#"Added Custom5",{"Month", "WeekNbr", "Day Of Week", "Date", "ProjectID", "EmployeeID", "NewHours", "Weekend?"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns0",{{"NewHours", "Hours"}})
    
in
    #"Renamed Columns"