Creating Date Schedules using M

[dt_mc]

Hi

suppose i have a table with 3 fields - ID, StartDate, EndDate

suppose 1 | 1/4/2017 | 4/15/2017

Is there a way to use that to create monthly row data for each month in the time frame?  would be great if start/end align so something like this:

ID | Date 

1 | 1/4/2017

1 | 2/28/2017

1 | 3/31/2017

1 | 4/15/2017

[Anonymous]

I would try using the Unpivot function.  You'll find it in the transform tab of the ribbon menu.

[dt_mc]

but would unpivot be able to create the rows for the months between start and end dates? wouldn't that only create two rows (start end)

[ChrisMendoza · Accepted solution]

@dt_mc,

Review the function by @ImkeF at https://www.thebiccountant.com/2017/12/11/date-datesbetween-retrieve-dates-between-2-dates-power-bi-.... Possibly this is the solution you need.

[Interkoubess]

@dt_mc,

Please try this formula ( change to adapt with your source):

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"StartDate", type date}, {"EndDate", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [EndDate]-[StartDate],Int64.Type),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type1", "Custom.1", each Number.IntegerDivide([Custom],30)+1),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each List.Repeat(#"Changed Type1"[StartDate],[Custom.1])),
    #"Expanded Custom.2" = Table.ExpandListColumn(#"Added Custom2", "Custom.2"),
    #"Added Index" = Table.AddIndexColumn(#"Expanded Custom.2", "Index", 1, 1),
    #"Added Custom3" = Table.AddColumn(#"Added Index", "Custom.3", each if [Index]=1 then [StartDate] else if [Index]=[Custom.1] then [EndDate] else Date.EndOfMonth(Date.AddMonths([StartDate],[Index]-1))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom3",{"ID", "Custom.3"})
in
    #"Removed Other Columns"

Hope it helps...

Ninter