Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 c...
Need Help
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    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.

PhilipTreacy's avatar
PhilipTreacy
Super User
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      PhilipTreacy
      Super User
      5 years ago

      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's avatar
        Anonymous
        Not applicable
        5 years ago

        Hello PhilipTreacy . Thank you! It works 🙂