Hello,
I have created a calculated column end date based on the next records start date in DAX - 
using this DAX code -
My requirement is to perform this exact operation within query editor so that I can utilize query editor to additionally fill in the dates between.
Any insight would be greatly appreciated. Thanks!
@adamhorowitz
Possible to upload some data? This is definitely doable in PQ for sure. I have done this before and it’s a combination of List.Dates and #duration.
I believe I know how to fill in the dates between, I am just stuck on recreating the “end date” column within query editor. The data only contains “Stage Start” not “Stage End” which I created in DAX to test and show my desired output.
Here is a sample of the data that is the stage history for one event:
Stage Data.xlsx (8.8 KB)
I attached the pbix file below so you can step through the applied steps, but here’s the general idea:
Group the rows by ID (I assumed there would be more than one ID in reality) and aggregate by all rows
Add an index column to each sub-table:
Table.AddIndexColumn(
[All Data], “Index”,1,1)
Remove the other columns, and expand the table with the index out
Add a new column that will provide the start date of the next column based on the index
List.Range( #"Expanded Index"[Start Date], [Index.1],1)
Expand that list out ( it will only be 1 record) and have your end date. Though once the IDs change, that first new ID row will have an incorrect end date. So we check for that with a new column
= Table.AddColumn(#"Expanded Custom", "Custom.1", each if [Custom] >=[Start Date] then [Custom] else null)
Now that we have the correct end date, we can now build a list of all the dates that are between those two.
try
List.Dates(
[Start Date],
Duration.Days([Stage End] - [Start Date] ) + 1,
#duration( 1, 0,0,0)
)
otherwise
List.Dates(
[Start Date],
1,
#duration( 1, 0,0,0)
)
We have to account for a null in the end date, which is what the “otherwise” is account for.
Expand that new column out. Remove all the misc columns and set data types and you should be good to go.
File:
Calculate End Date in Query Editor.pbix (11.8 KB)
Thanks to the contributors of this post. Due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread.
Thanks to the contributors of this post. Due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread.
