Formulae to copy/paste dates based on conditions

[Fab117]

Hi,

Sorry, title of this post is not so clear, but didn't find better.

I've the table "Tasks-TasBaselines - Projects" with fields:

• "ProjectId" text field
• "Project Phase" text field
• "earliestStartDate" date field
• "latestDueDate" date field

Something looking like:

ProjectId	Project Phase	earliestStartDate	latestDueDate
A	Initiation	25.09.2023	15.10.2023
A	Planning	10.10.2023	12.12.2023
A	Execution and control	01.01.2024	15.03.2024
A	Closure	15.03.2024	28.03.2024
B	Initiation	15.08.2023	25.08.2023
B	Planning	12.08.2023	30.08.2023
B	Execution and control	15.09.2023	22.11.2023
B	Closure	25.11.2023	15.01.2024
C	Initiation	12.11.2023	15.12.2023
C	Planning	15.01.2024	15.02.2024
C	Execution and control	10.02.2024	25.05.2024
C	Closure	15.06.2024	15.07.2024

I'd like to create a new custom column "earliestStartDateCorrected" where,

For each ProjectId I retrieve latestDueDate from previous "Project Phase" for "Project Phase" = "Planning", "Project Phase" = "Execution and control" and "Project Phase"  = "Closure".

NB: For "Project Phase" = "Initiation", it should retrieve "earliestStartDate".

To illustrate it:

Could someone help me with the formulae of the custom column?

Thanks in advance.

Fab

[v-xinruzhu-msft]

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.

[Fab117]

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

[AlienSx]

Hello, @Fab117 

let
    Source = your_table,
    f = (tbl as table) =>
        [a = Table.ToColumns(tbl),
        b = List.FirstN(a{2}, 1) & List.FirstN(a{3}, 3),
        out = Table.FromColumns(a & {b}, Table.ColumnNames(tbl) & {"earliestStartDateCorrected"})][out],
    gr = Table.Group(Source, {"ProjectId"}, {{"all", (x) => f(Table.Sort(x, "earliestStartDate"))}}),
    expand = Table.ExpandTableColumn(gr, "all", {"Project Phase", "earliestStartDate", "latestDueDate", "earliestStartDateCorrected"})
in
    expand

[Fab117]

Hi @AlienSx,

Big thanks also to you for investing time on my issue.

It's still pending. Seems that I missed something with your proposal.

My database name is "Tasks-TasBaselines - Projects"

=> in your code, I first replaced

Source = your_table,

with:

Source = "Tasks-TasBaselines - Projects"

But got "error" as return

Details of the error:

I then removed the quotation marks round my table name:

But faced another error message:

I then changed the name of my table. From "Tasks-TasBaselines - Projects" "TestNewNameForTable"

Error details encoutered with the quotation mark:

Detailed error message without the quotation mark:

Would you know what I'm doing wrong?

Thanks.

Fab

[AlienSx]

@Fab117 you are trying to add custom column - bad decision, don't do that. Lets create new query. Suppose you loaded your original table into PQ. The name of the query is Tasks-TasBaselines - Projects (can be anything).

Create a new blank query New Source -> Other Sources -> Blank Query.

On your left find Advanced Editor and step on it. Advanced Editor window shows up. 

Replace everthing inside with the code I gave you while your_table string should be replaced by

#"Tasks-TasBaselines - Projects" (see image). Press Done.

[Fab117]

Big thank you for the time invetested on my issue.

Finally, I followed the 1st answer and was able to solve my issue

[ImkeF]

Hi @Fab117 ,
if we cannot rely on the correct order and completeness of the rows in each Project, then this would be the secure approach:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZDBCoMwDIZfZXiWksZ1c8dNdthtd/EgTkZBUnAKe/xFumqjCL38zZfma8oyuSZp8iA72HqwjjigUXBRCJhx0EZp8KFKPfzsaiJL76kKc5UDKj4CvX/bZpyePdT0OjSOht51fA9a8WH06EdA5kPoKzr3GftWFtksF+RtbT7BedDBJQQ4NscIzWCD7pnreDnIP9aybzHn+aHo2/RiXmzMUcLxIouVuVntDuW7u+Ywo345RvbJnZ/iCec/Wf0A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ProjectId = _t, #"Project Phase" = _t, earliestStartDate = _t, latestDueDate = _t]),
#"Changed Type" = Table.TransformColumnTypes(
    Source, 
    {
      {"ProjectId", type text}, 
      {"Project Phase", type text}, 
      {"earliestStartDate", type date}, 
      {"latestDueDate", type date}
    }
  ), 
  #"Grouped Rows" = Table.Group(
    #"Changed Type", 
    {"ProjectId"}, 
    {
      {
        "Partition", 
        each 
          let
            BufferedTable = Table.Buffer(_), 
            AddColumn = Table.AddColumn(
              BufferedTable, 
              "earliestStartDateCorrected", 
              each 
                if [Project Phase] = "Initiation" then
                  [earliestStartDate]
                else if [Project Phase] = "Planning" then
                  Table.SelectRows(BufferedTable, (r) => r[Project Phase] = "Initiation"){0}[
                    latestDueDate
                  ]
                else if [Project Phase] = "Execution and control" then
                  Table.SelectRows(BufferedTable, (r) => r[Project Phase] = "Planning"){0}[
                    latestDueDate
                  ]
                else if [Project Phase] = "Closure" then
                  Table.SelectRows(BufferedTable, (r) => r[Project Phase] = "Execution and control"){
                    0
                  }[latestDueDate]
                else
                  null
            )
          in
            AddColumn
      }
    }
  ), 
  #"Expanded Partition" = Table.ExpandTableColumn(
    #"Grouped Rows", 
    "Partition", 
    {"Project Phase", "earliestStartDate", "latestDueDate", "earliestStartDateCorrected"}, 
    {"Project Phase", "earliestStartDate", "latestDueDate", "earliestStartDateCorrected"}
  )
in
  #"Expanded Partition"

Replace the code in step "Source" by a reference to your data source.

[Fab117]

Hi @ImkeF,

Thank you for spending time on my issue.

It seems that there something I missed.

I applied your approach to my case, but didn't get expected results.

Below some print screen (I filtered only on one project and hide non relevant columns.

1. Base:

2. After adding the custom column with your code:

3. Additional columns for extension:

4. Result:

Would you know what I do wrong?

tks.

Fab

[ImkeF · Accepted solution]

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-co...

[Fab117]

@ImkeF,

First thank you for the link to your video (very helful for beginners like me).

I followed your steps and I got what I was looking for.

=> solved.

Thanks a lot for your help.

Fab