Forum Discussion

Kacol's avatar
Kacol
Regular Visitor
2 years ago

How can a certain query take a specific date from another query?

I have a certain query with lots of past and future dates, 
also I have added columns in it. Let's call it query number 1

My goal is that query number 1 will take a specific date from another query, 
which is called query number 2,
this date must be yesterday's date. 
So I created a formula in query number 2 that would get me the date directly. 

My question is how do I make the dates in query number 1 filter 
according to the specific date I put in query number 2?

 

General Comment
Need Help

4 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

    Kacol , Create a new query 2 

     

      let
             Yesterday = Date.From(DateTime.LocalNow()) - #duration(1, 0, 0, 0)
         in
             Yesterday
     
    This will give yesterday's date
     
    Create a relation between Query 1 and Query 2
     
    Create a measure in Query 1 to filter on the basis of Query 2
     
    FilteredData =
    CALCULATE(
    COUNTROWS(Query1),
    Query1[DateColumn] = VALUES(Query2[Yesterday])
    )
     
    And use this measure in visuals
    • Kacol's avatar
      Kacol
      Regular Visitor
      2 years ago

      Thanks for the answer, but my problem is even more complex than I thought. 

      I simply import data through external software, some of the tables include time, financial data, stocks, etc. Only from the moment I import the "time column" can I continue to import additional columns. The dates in "Time" are not defined as a date but as text. Once I change it from text to date format, I can't go ahead and import more data. That's why it's complicated. Is there some way to still filter the time so that it gives me yesterday's automatic date? The dates that appear are in the past and in the future. 

  • Kishore_KVN's avatar
    Kishore_KVN
    Icon for Solution Sage rankSolution Sage
    2 years ago

    Hello Kacol ,

    Don't create query2 with yesterdays date. Instead of that create a parameter in Power Query Editor for yesterday's date and use that parameter to filter this table. 

    Please refer below video for details:

    Power Query Parameters - Dynamically Change the Output of Your Queries Depending on Their Value - YouTube


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

    • Kacol's avatar
      Kacol
      Regular Visitor
      2 years ago

      Thanks for trying to help, but it still didn't solve my problem. I can't figure out how I still filter query number 1, what I saw in the video is just a parameter in the form of text.

      I'm still not sure how to automatically filter by yesterday's date.