Forum Discussion
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.
Your answers and solutions are very appreciated. Thank you!
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.
3 Replies
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.Thank you very much! Awesome!
Glad I could help Reto_Swiss
