Forum Discussion

rlymer's avatar
rlymer
Frequent Visitor
3 years ago
Solved

Power Query for Calculated Column - Last Review Date

I have 2 tables, Task and Review. Task has TaskID as a primary key, and Review has TaskID as a foreign key. Each Task can have multiple Reviews.   I am wanting a column in the Task table to show th...
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    3 years ago

    Hi rlymer 

     

    Download example PBIX file

     

    I know you have posted in the PQ forum but I think this is better off done in DAX as you don't have to add a new column to your dataset. And it'll be faster than doing a table joins if your data set is large.

     

    Try this measure

    Latest Review Date = CALCULATE(MAX('Review'[Review Date]), FILTER('Task', 'Task'[TaskID] = SELECTEDVALUE('Task'[TaskID])))

     

    On this Review table

     

    Giving this (the TaskID column is set to show items with no data)

     

     

    Regards

     

    Phil

wdx223_Daniel's avatar
wdx223_Daniel
Community Champion
3 years ago

NewStep=Table.TransformColumns(Table.NestedJoin(Task,"TaskID",Review,"TaskID","LastReviewDate",JoinKind.LeftOuter),{"LastReviewDate",each if _ is table then List.Max([ReviewDate]) else null})