Comparing field to data in another table power query excel

Hi HeideDeLange  - this is how you would replicate the Excel formula you posted but with Power Query.

Table.AddColumn(#"Your Previous Step Name", "CompareDates", each if [Date2] < [Date1] then "Before" else "After", type text)

In this example, there is a table with two date columns.  If Date2 is less than Date1 then Before else After.

Here is the complete script.  Create a new blank query, open the Advanced Editor, replace the entire contents with this script.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstC31DcyMDJR0lGy1DeEMGN1opXMYRwdJTN9YwMUCVOYDJBtBpWJBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date1 = _t, Date2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date1", type date}, {"Date2", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "CompareDates", each if [Date2] < [Date1] then "Before" else "After", type text)
in
    #"Added Custom"

Hi HeideDeLange, check this:

 

Table1SingleDate

 

Output

let
    Table1SingleDate = #table(type table[Date of Extract=date], {{#date(2024,1,15)}}),
    Table2Data = Table.FromList(List.Dates(#date(2024,1,1), 31, #duration(1,0,0,0)), (x)=> {x}, type table[Expiry Date=date]),
    Ad_Check = Table.AddColumn(Table2Data, "Check", each if [Expiry Date] < Table1SingleDate{0}[Date of Extract] then "Before" else "After", type text)
in
    Ad_Check