Forum Discussion

HeideDeLange's avatar
HeideDeLange
Regular Visitor
2 years ago
Solved

Comparing field to data in another table power query excel

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 requir...
Need Help
  • HeideDeLange's avatar
    HeideDeLange
    2 years ago

    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!

jennratten's avatar
jennratten
Super User
2 years ago

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's avatar
    HeideDeLange
    Regular Visitor
    2 years ago

    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.