Need to filter one column based on value in another query

[Anonymous]

Hello!

I have a rather strange idea to do.

I have one table query like this:

And another table query with a column like this:

I need to apply a filter to this column to have a close date between the period start and period end.

Tables related as 1-to-many.

Is it possible?

Thank you!

[PhilipTreacy]

Hi @Anonymous 

Assuming that your first query is called Periods then you can filter the Close date column in your 2nd query with this

    #"Filtered Rows" = Table.SelectRows(PreviousStepName, each ([Close date] >= Periods[Period start]{0} and [Close date] <= Periods[Period end]{0}))

Phil 

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Anonymous]

Hello @PhilipTreacy ,

Thank you for your reply. Unfortunately, it didin't work for me. Maybe I do something wrong. Let me explain one more time. I have Excel file with 2 tables as source file. Can't attach files here, but I uploaded it to Google Drive if required

After export table1 looks like:

Table 2 looks like:

What I need to do: I need to filter column "Closed date" of Table2 with dates between "Start" and "End" of Table1.

Is it possible?

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

Attached are an Excel workbook and a PBIX file containing the 2 queries that filter as you require.

The first query loads the Start and End dates.

The 2nd query loads the table of dates/tickets and filters using the dates from the first query.

In the M code in the PBIX file, you will need to change the folder that the file is loaded from.

Query 1 - Load Start and End Dates

let
    Source = Excel.Workbook(File.Contents("D:\temp\bogachev\bogachev.xlsx"), null, true),
    Start_End_Table = Source{[Item="Start_End",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Start_End_Table,{{"Start", type datetime}, {"End", type datetime}})
in
    #"Changed Type"

Query 2 - Load Data Table and Filter

let
    Source = Excel.Workbook(File.Contents("D:\temp\bogachev\bogachev.xlsx"), null, true),
    Tickets_Table = Source{[Item="Tickets",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Tickets_Table,{{"Closed date", type datetime}, {"Ticket #", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Closed date] >= Start_End[Start]{0} and [Closed date] <= Start_End[End]{0})
in
    #"Filtered Rows"

Regards

Phil

---

If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

[Anonymous]

Hello @PhilipTreacy . Thank you! It works 🙂

[Greg_Deckler]

@Anonymous - So, I am not entirely clear on what exactly you are trying to accomplish but this DAX will return all of the close dates within a specified range:

Table =
  VAR __Start = MAX('Table'[Period start])
  VAR __End = MAX('Table'[Period end])
RETURN
  FILTER(ALL('Table2'),[Close date] >= __Start && [Close date] <= __End)

[Anonymous]

Hello @Greg_Deckler .

Actually, I need to do it on Power Query, not via measure for table.

No idea how to put DAX in query 😞

[Greg_Deckler]

@Anonymous Sorry, didn't pay attention to the forum this was posted in, @ImkeF @edhans