cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ThomasWeppler
Helper V
Helper V

Add extra row in power query based on a date

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

closed and opened.png


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 🙂

1 ACCEPTED SOLUTION
rohit_singh
Super User
Super User

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.

rohit_singh_0-1654851006102.png

 

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.

rohit_singh_1-1654851152183.png

 

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.

 

rohit_singh_2-1654851255650.png

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

rohit_singh_3-1654851293257.png

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

rohit_singh_4-1654851349122.png

 

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

rohit_singh_5-1654851390010.png

 

7) Select all columns and click on Remove duplicates

rohit_singh_6-1654851428793.png

 

😎 This will give you your final dataset

rohit_singh_7-1654851465432.png

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

View solution in original post

4 REPLIES 4
Vijay_A_Verma
Super User
Super User

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"

 

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

rohit_singh
Super User
Super User

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.

rohit_singh_0-1654851006102.png

 

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.

rohit_singh_1-1654851152183.png

 

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.

 

rohit_singh_2-1654851255650.png

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

rohit_singh_3-1654851293257.png

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

rohit_singh_4-1654851349122.png

 

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

rohit_singh_5-1654851390010.png

 

7) Select all columns and click on Remove duplicates

rohit_singh_6-1654851428793.png

 

😎 This will give you your final dataset

rohit_singh_7-1654851465432.png

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

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

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Top Kudoed Authors