Forum Discussion
Formulae to copy/paste dates based on conditions
Hi Fab117 ,
yes, you applied the code to each row of the table instead of just to the table itself.
Easiest would be to:1) Create a new query
2) Copy my query code into the advanced editor (replacing everything that exists there so far)
3) Replace the content of the first "Source"-step by a reference to your query
If you run into problems with that, please watch my video here: http://community.powerbi.com/t5/Webinars-and-Video-Gallery/Power-BI-Forum-Help-How-to-integrate-M-code-into-your-existing/m-p/179314
Hi Fab117
You can refer to the following solution.
1.Create a new step after "Changed Type"
2.Then input the following code to new step and rename the new step to "Grouped Rows"
= Table.Group(#"Changed Type", {"ProjectId"}, {{"Data", each Table.AddIndexColumn(_, "Index", 1, 1), type table}})
3.Create a new step after "Grouped Rows" and input the following code to new step and rename the new step to "Expanded Data"
= Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Project Phase", "earliestStartDate", "latestDueDate", "Index"}, {"Project Phase", "earliestStartDate", "latestDueDate", "Index"})
4.Add a custom column and input the following code
if [Index]=1 then [earliestStartDate] else List.Min(
Table.SelectRows(#"Expanded Data",(x)=>x[ProjectId]=[ProjectId] and x[Index]=[Index]-1)[latestDueDate])
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for your support. In particular for the demo file you created (so helpful for beginners like me)
I tested your proposal, but was not able to get the expected result with my base.
I've finally been able to implement successfuly the 1st solution proposed upper.
Fab