Forum Discussion

dt_mc's avatar
dt_mc
Frequent Visitor
8 years ago
Solved

Creating Date Schedules using M

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 ...
Need Help
Tutorial Requests
dt_mc's avatar
dt_mc
Frequent Visitor
8 years ago

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)

  • Interkoubess's avatar
    Interkoubess
    Icon for Solution Sage rankSolution Sage
    8 years ago

    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