Forum Discussion

EaglesTony's avatar
EaglesTony
Icon for Post Prodigy rankPost Prodigy
3 years ago
Solved

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

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 

Need Help
  • Manoj_Nair's avatar
    Manoj_Nair
    3 years ago

    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/

1 Reply

  • Manoj_Nair's avatar
    Manoj_Nair
    Icon for Solution Supplier rankSolution Supplier
    3 years ago

    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/