Comparing field to data in another table power query excel

[HeideDeLange]

Hi all,

I am really hoping you can help me. I have tried everything that I could find in these forums, and I am most likely doing something wrong.  I use Power query in Excel.

I have a requirement where I need to compare a column in Table 1 with a single date which is the extract date. 

I have made extarct date a table "ExtractDate" in order to use it in Power query. This date changes everytime the process is run, so I don't want to hardcode it in the formula.

Date of extract

2024/08/15

If I were to use normal excel, I simply I would apply the following formula:

If "Expiry date" < "Date of Extract" then "Before" else "After"

My table that contains expiry date column has many lines and other columns too. 

I have tried Lookupvalue (my power query says it doesn't know this function). I have attempted to merge the tables but there is no column that is a perfect match. I am a little out of my depth now.

Any ideas as to how to do this please?

[HeideDeLange · Accepted solution]

Hi all, Thank you for your suggestions. Neither helped because the Date information is in a cell in the workbook, that then gets sucked in to a query, however my data table and the Date table still live in different queries.

However I did find a solution that works:

Table.FirstValue(Date) did the job. 

Thanks for your assistance!

[dufoq3]

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

[jennratten]

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"

[HeideDeLange]

The problem I have is that the dates are in 2 different tables. 

Table 1 has a list of 10000 dates that I need to compare against Table 2 that has only 1 entry of a single date.