Add extra row in power query based on a date

Hello ThomasWeppler ,

Please follow the steps below :

1) You need to compute the final end date in order to display the months between start and end dates. For this you will need to add a custom column that calculates end date. If start date < closed date, then it takes current date else closed date. Please note that we will be using this column as the end date going forward and not the closed date column.

2) Once you've added the final end date you will need to add two columns that give you start of the month, one each for start date and end date.

3) Next, we create a column with a list of dates between start and end dates. These dates will be in numeric format.

Expand the list and add values to new rows. This will give you additional rows by date.

4) Change the datatype of the date column from numeric to date.

5) Convert the date column int start of the month in order to give you the month.

6) Select the below columns and click on remove other columns.

7) Select all columns and click on Remove duplicates

😎 This will give you your final dataset

Here is the M-code. You can copy and paste this into a blank query and see each step in detail.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjI2VtJRMjDUNzDSNzIwMoJyTCCcWB2gChNDbCoM9Q0tzQ2UYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [assisgnmentid = _t, startdate = _t, closeddate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"assisgnmentid", Int64.Type}, {"startdate", type date}, {"closeddate", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "enddate", each if [closeddate] < [startdate] then Date.From(DateTime.FixedLocalNow()) else [closeddate]),
    #"Inserted Start of Month" = Table.AddColumn(#"Added Custom", "MonthStart", each Date.StartOfMonth([startdate]), type date),
    #"Inserted Start of Month1" = Table.AddColumn(#"Inserted Start of Month", "MonthEnd", each Date.StartOfMonth([enddate]), type date),
    #"Added Custom1" = Table.AddColumn(#"Inserted Start of Month1", "Custom", each {Number.From([MonthStart])..Number.From([MonthEnd])}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom1", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"Custom", "Date"}}),
    #"Calculated Start of Month" = Table.TransformColumns(#"Renamed Columns",{{"Date", Date.StartOfMonth, type date}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Calculated Start of Month",{"assisgnmentid", "startdate", "enddate", "Date"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns")

in
    #"Removed Duplicates"

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂