cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

New Member

## To append/merge/add Rows based on a start and an end date.

How could I solve this problem best.

My table is looking like that before:

After the process I should have a copy of the rows with all the dates between Start and End.
How I solve this Problem the best way.

1 ACCEPTED SOLUTION
Super User

The following formula in a new column will generate all dates between the start and end date.

``List.Dates([Start], Duration.Days([End] - [Start]), #duration(1,0,0,0))``

Then expand to new rows.

becomes this:

Full code sample:

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8spPVdJRMtQ31DcyMDKAM41BTAM9U6VYnWil4MRcsIQpSI0hRI0BiG0EYivFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Start = _t, End = _t, Pension = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}}),
#"Added Date LIst" = Table.AddColumn(#"Changed Type", "Date List", each List.Dates([Start], Duration.Days([End] - [Start]), #duration(1,0,0,0))),
#"Expanded Date List" = Table.ExpandListColumn(#"Added Date LIst", "Date List")
in
#"Expanded Date List"``````

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

If that isn't what you need, provide a clearer explanation per info below.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
3 REPLIES 3
Super User

The following formula in a new column will generate all dates between the start and end date.

``List.Dates([Start], Duration.Days([End] - [Start]), #duration(1,0,0,0))``

Then expand to new rows.

becomes this:

Full code sample:

``````let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8spPVdJRMtQ31DcyMDKAM41BTAM9U6VYnWil4MRcsIQpSI0hRI0BiG0EYivFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Start = _t, End = _t, Pension = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Start", type date}, {"End", type date}}),
#"Added Date LIst" = Table.AddColumn(#"Changed Type", "Date List", each List.Dates([Start], Duration.Days([End] - [Start]), #duration(1,0,0,0))),
#"Expanded Date List" = Table.ExpandListColumn(#"Added Date LIst", "Date List")
in
#"Expanded Date List"``````

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

If that isn't what you need, provide a clearer explanation per info below.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
New Member

Thank you very much! Awesome!

Super User

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors