Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThe 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank you very much! Awesome!
Glad I could help @Reto_Swiss
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingStarting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
24 | |
13 | |
12 | |
11 | |
8 |
User | Count |
---|---|
43 | |
25 | |
16 | |
15 | |
12 |