Forum Discussion

Helper IV

2 years ago

Solved

Formulae to copy/paste dates based on conditions

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 "earli...

Need Help

ImkeF
2 years ago
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

AlienSx

Super User

2 years ago

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
Helper IV
2 years ago
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
  Super User
  2 years ago
  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
    Helper IV
    2 years ago
    Big thank you for the time invetested on my issue.
    Finally, I followed the 1st answer and was able to solve my issue