Add column with Max date from another table

[jimrward]

I have a master table with 2 columns ID, Date, I also have another table called LastPM with a column called LastPM that contains a simple date in the form dd/mm/yyyy and it has 2 rows
10/9/2023
14/1/2024

I would like to create an additional column in my master table containing either the Max of table LastPM or to cut out some steps a conditional column
=if mastertable.Date > table.max(LastPM) then 1 else 0

I can’t seem to get the syntax correct, tried excel.currentworkbook(….

I can hardcode the row using {0} or {1} but I would like it to be a bit more flexible to choose Max so I can keep adding dates as and PM, preventative Maintenance is done

 

[etaf]

how about
=IF(B2>MAX($E$2:$E$3),1,0)

Book3
	A	B	C	D	E
1	ID	Date			Last PM
2	A1	1/1/24	0		9/10/23
3	A2	1/2/24	0		1/14/24
4	A3	1/3/24	0
5	A4	1/4/24	0
6	A5	1/5/24	0
7	A6	1/6/24	0
8	A7	1/7/24	0
9	A8	1/8/24	0
10	A9	1/9/24	0
11	A10	1/10/24	0
12	A11	1/11/24	0
13	A12	1/12/24	0
14	A13	1/13/24	0
15	A14	1/14/24	0
16	A15	1/15/24	1
17	A16	1/16/24	1
18	A17	1/17/24	1
19	A18	1/18/24	1
20	A19	1/19/24	1
21	A20	1/20/24	1
22	A21	1/21/24	1
Sheet1

Cell Formulas
Range	Formula
C2:C22	C2	=IF(B2>MAX($E$2:$E$3),1,0)

 

[jimrward]

@etaf I can that in excel as you point out above I am trying to do this in Power query

 

[RoryA]

Assuming you've loaded the table in another query (or as a step in your current one) then you can use

List.Max(queryname[LastPM])

 

[jimrward]

@RoryA thanks I might rename my column in the LastPM table as it was also called LastPM and check the data type is set to date only in case things were not fully qualified
I was using what you suggest st some point and getting errors about not being able to convert table to list and then after more adjustments I would get errors about date conversion
I will report back later with code snippet