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

[Reto_Swiss]

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

[edhans · Accepted solution]

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
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.

[Reto_Swiss]

Thank you very much! Awesome!

[edhans]

Glad I could help @Reto_Swiss