Add extra row in power query based on a date

[ThomasWeppler]

Hi power Bi community
I am new to m so this might be an easy question.
But the help will still be greatly appreciated

I have a table with three colums

Assignmentid is an individual id for each assignment

Startdate is the date the assingment start

Closed date is the date the assignment is closed if the assignment is still open it will return 01/01/1970

Here is an example

I want to add a row for each month for each assignment until the assignment is closed.
So if assignmentid 233 starts 01/02/2022 and is closed 01/04/2022
I want to expaned it to three rows like this

assignmentid 233; startdate 01/02/2022; closeddate; 01/04/2022; date 01/02/2022
assignmentid 233; startdate 01/02/2022; closeddate; 01/04/2022; date 01/03/2022
assignmentid 233; startdate 01/02/2022; closeddate; 01/04/2022; date 01/04/2022

if the assignment hasn't been closed yet like assignmentid 241 I want to yet a row until the quretnt date like this
assignmentid 241; startdate 01/02/2022; closeddate; 01/04/2022; date 01/02/2022
assignmentid 241; startdate 01/02/2022; closeddate; 01/04/2022; date 01/03/2022
assignmentid 241; startdate 01/02/2022; closeddate; 01/04/2022; date 01/04/2022
assignmentid 241; startdate 01/02/2022; closeddate; 01/04/2022; date 01/05/2022
assignmentid 241; startdate 01/02/2022; closeddate; 01/04/2022; date 01/06/2022

I really hope someone can help me with this 🙂

[Vijay_A_Verma]

Put this formula in a custom column and then expand the column into rows

= List.Generate(()=>[x=[startdate],y=if [closedate]=#date(1970,1,1) then Date.From(DateTime.FixedLocalNow()) else [closedate]], each [x]<=[y], each [y=[y],x=Date.AddMonths([x],1)], each [x])

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjI2VtJRMtI31DcyMDICMk1gzFgdoKyJIaqsIZBpaGluAJE1tUSVNUPRa2aAT6+5CaosnBkbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [assignmentid = _t, startdate = _t, closedate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"assignmentid", Int64.Type}, {"startdate", type date}, {"closedate", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Generate(()=>[x=[startdate],y=if [closedate]=#date(1970,1,1) then Date.From(DateTime.FixedLocalNow()) else [closedate]], each [x]<=[y], each [y=[y],x=Date.AddMonths([x],1)], each [x])),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

[ThomasWeppler]

I went with Rohit solution, but I still wanted to give you a kudo and thank you for your help.

[rohit_singh · Accepted solution]

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! 🙂

[ThomasWeppler]

It works.
Thanks a ton.
You are a hero 🙂