Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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!
Solved! Go to 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.
Proud to be a 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.
Proud to be a Super User!
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?
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.
Proud to be a Super User!
@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 😞
@Anonymous Sorry, didn't pay attention to the forum this was posted in, @ImkeF @edhans
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
15 | |
12 | |
8 | |
8 | |
7 |
User | Count |
---|---|
15 | |
13 | |
9 | |
7 | |
6 |