Power Query for Calculated Column - Last Review Date

[rlymer]

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 the latest Review date, or null if there has been no review. Can anyone point me in the right direction in terms of the power query?

Thanks

[PhilipTreacy · Accepted solution]

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

[rlymer]

@PhilipTreacy Thank you, this works great. I had to explicitly state the relationship with USERELATIONSHIP as in my model it was an inactive relationship.

[wdx223_Daniel]

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