Forum Discussion
Need to filter one column based on value in another query
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!
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.
7 Replies
- PhilipTreacy
Super User
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.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
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.
- Greg_Deckler
Community Champion
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)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
