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
Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

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
      Solution 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