I need help to filter data between 2 tables based on a where clause

[EaglesTony]

Hi,

  I have tableA with a column called STARTDATE and ENDDATE and tableB with a column called STARTDATE and ENDDATE.

  tableB has an STARTDATE of 4/12/2023 and ENDDATE of 6/20/2023.

  I want to select records from tableA where the STARTDATE >= tableB.STARTDATE and ENDDATE <= tableB.ENDDATE 

[Manoj_Nair · Accepted solution]

@EaglesTony- Try this solution in Power Query Advanced Editor. Follow these steps:-

1. In Power Query, go to the 'Home' tab, and click on 'Advanced Editor'.
2. Paste the following M language code and click on 'Done':

let
    // Load TableA
    TableA = #"Your TableA source here",
    // Load TableB
    TableB = #"Your TableB source here",
    // Get STARTDATE and ENDDATE from TableB
    TableB_STARTDATE = TableB{0}[STARTDATE],
    TableB_ENDDATE = TableB{0}[ENDDATE],
    // Filter TableA based on TableB's dates
    FilteredTableA = Table.SelectRows(TableA, each [STARTDATE] >= TableB_STARTDATE and [ENDDATE] <= TableB_ENDDATE)
in
    FilteredTableA

 Remember to replace #"Your TableA source here" and #"Your TableB source here" with the actual source steps for TableA and TableB.

If this post helps to find solution would be happy if you could mark my post as a solution and give it a thumbs up

Best regards

Manoj Nair

Linkedin - https://www.linkedin.com/in/manoj-nair-%E2%98%81-344666104/