Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
23 | |
10 | |
10 | |
9 | |
9 |