Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ThomasWeppler
Skilled Sharer
Skilled Sharer

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

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

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors