cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Taddi10
Helper III
Helper III

Power Query : End date from the next Start Date

Hello All,

 

I have an issue and I will apreciate your Helps!

 

I need to calculate the DateEndPhase column using Power Query (M) based on the DateCreated column of the next row for the same Taskname as shown in this table :

question Power Query.JPG

Thanks in advance for your replies,

Taddi10

1 ACCEPTED SOLUTION
edhans
Super User III
Super User III

If your data source isn't too large, you can use this method. Add two INDEX columns, one that starts at 0, one that starts at 1.

Then merge the table to itself like this:

edhans_0-1623975235763.png

When you expand the Date Created column, that will be your End Phase Date. Here is the table immedately after the merge - note that the sort order changes. We'll fix that in a minute.

edhans_1-1623975299841.png

Now just sort by the task and date created fields, and keep only the necessary fields.

edhans_2-1623975362829.png

Here is the M code to see how it works:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTLUN9Q3MjAyADKVYnUgYkZoYklAhjEWMRM0sWQgwxRNLAXIMMMiZo4sFgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TaskName = _t, #"Date Created" = _t, Phase = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"TaskName", "Index.1"}, #"Added Index1", {"TaskName", "Index"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Date Created"}, {"Date Created.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Added Index1",{{"Date Created.1", "End Date"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"TaskName", Order.Ascending}, {"Date Created", Order.Ascending}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"TaskName", "Date Created", "Phase", "End Date"})
in
    #"Removed Other Columns"

If this is slow @Taddi10 let me know. There is another way to do this. It is MUCH more complex - requires manual coding. Still, not overly ornerous, but not necessary for a few hundred or even a few thousand records. But it would perform well over hundreds of thousands of records.

Also, in the future, please post actual data to use - images are great for expected results, but we have to key stuff in.

 

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.

 



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

2 REPLIES 2
CNENFRNL
Super User III
Super User III

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLUN9Q3MjAyUIrVgQgYIQs4oasACxhgiJgiizgDRVAEXMDGGqEKmMIFYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TaskName = _t, #"Date Created" = _t]),
    #"Grouped Rows" = Table.RemoveColumns(Table.Group(Source, {"TaskName"}, {{"ar", each Table.FromColumns(Table.ToColumns(_) & {List.Skip([Date Created],1) & {null}}, Table.ColumnNames(_) & {"DateEndPhase"})}}), "TaskName"),
    #"Expanded ar" = Table.ExpandTableColumn(#"Grouped Rows", "ar", {"TaskName", "Date Created", "DateEndPhase"}, {"TaskName", "Date Created", "DateEndPhase"})
in
    #"Expanded ar"

Screenshot 2021-06-18 112411.png

edhans
Super User III
Super User III

If your data source isn't too large, you can use this method. Add two INDEX columns, one that starts at 0, one that starts at 1.

Then merge the table to itself like this:

edhans_0-1623975235763.png

When you expand the Date Created column, that will be your End Phase Date. Here is the table immedately after the merge - note that the sort order changes. We'll fix that in a minute.

edhans_1-1623975299841.png

Now just sort by the task and date created fields, and keep only the necessary fields.

edhans_2-1623975362829.png

Here is the M code to see how it works:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTLUN9Q3MjAyADKVYnUgYkZoYklAhjEWMRM0sWQgwxRNLAXIMMMiZo4sFgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [TaskName = _t, #"Date Created" = _t, Phase = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"TaskName", "Index.1"}, #"Added Index1", {"TaskName", "Index"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"Date Created"}, {"Date Created.1"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Added Index1",{{"Date Created.1", "End Date"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"TaskName", Order.Ascending}, {"Date Created", Order.Ascending}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"TaskName", "Date Created", "Phase", "End Date"})
in
    #"Removed Other Columns"

If this is slow @Taddi10 let me know. There is another way to do this. It is MUCH more complex - requires manual coding. Still, not overly ornerous, but not necessary for a few hundred or even a few thousand records. But it would perform well over hundreds of thousands of records.

Also, in the future, please post actual data to use - images are great for expected results, but we have to key stuff in.

 

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.

 



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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Kudoed Authors