Forum Discussion

AnaBraganca's avatar
AnaBraganca
Frequent Visitor
2 years ago
Solved

Filter a query based on a another table

Hello,

I'm struggling to filter a database based on a date listed in another database.

I've seen similar problems here, but so far nothing worked, or I'm getting the coding wrong.

 

I have a database called Existencia, with several rows and three columns. One of the columns has dates, column Dat1. 

I have another database, called Analise,  and I want to filter the date column - called Dia - in the database Analise, to be on or before the latest date from Dat1 in the Existencia database. So I want ----->   Dia <= latest Dat1 

Note: The latest date is constantly changing, the Existencia database source is an Excel file, so new rows created constantly. 

 

I don't want to merge or append, and want to avoid creating another column in the Analise query to meet the condition.

But I'm struggling with the language to filter in the Advanced Editor.

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi AnaBraganca ,

    I think you can use Parameter in Power Query.

    Then you can choose Date Filters and select Before.

    Finally you can get the whole days before what you need.

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi AnaBraganca ,

    I create two tables as you mentioned, their names are Existencia and Analise.

    Then I create a new table and it satisfies your requirements.

    Table =
VAR LatestDate =
    MAX ( 'Existencia'[Date1] )
RETURN
    FILTER ( 'Analise', 'Analise'[Dia] <= LatestDate )

     

     

     

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • AnaBraganca's avatar
      AnaBraganca
      Frequent Visitor
      2 years ago

      Hi,

      I'm trying to avoid creating another table.

      What I'm trying is for this filter to be each [Data] <= the latest date of the Dat1 column (second picture). But this second part of the code is the one I'm struggling with.

       

      This is the table with the date I want to filter with.

       

       

      In this case, I want the code to filter by 30-06-2023, which will change when a new row is added. 

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi AnaBraganca ,

        I think you can use Parameter in Power Query.

        Then you can choose Date Filters and select Before.

        Finally you can get the whole days before what you need.

         

         

         

        Best Regards

        Yilong Zhou

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.