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
Reto_Swiss
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:

Reto_Swiss_0-1677709371087.png

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.
Your answers and solutions are very appreciated. Thank you!

1 ACCEPTED SOLUTION
edhans
Super User
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.

edhans_0-1677716003108.png

becomes this:

edhans_1-1677716061483.png

 

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
5) See this article if you need help using this M code in your model.

 

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

 

How to get good help fast. Help us help you.

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 I answer your question? Mark my post as a solution!
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

View solution in original post

3 REPLIES 3
edhans
Super User
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.

edhans_0-1677716003108.png

becomes this:

edhans_1-1677716061483.png

 

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
5) See this article if you need help using this M code in your model.

 

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

 

How to get good help fast. Help us help you.

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 I answer your question? Mark my post as a solution!
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

Thank you very much! Awesome!

Glad I could help @Reto_Swiss 



Did I answer your question? Mark my post as a solution!
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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors