none
Power Query | Filter Row X in dependency to row Y RRS feed

  • Question

  • Lets say I habe three rows:

    • Project
    • Employee
    • Task

    The Column project include different project. In every project we have different employees involved in different tasks.

    Here comes an example of one Task:
    • Project: Donut
    • Employee: Laurent
    • Task: Put Chocolate on top

    A row could look like this: Donut; Laurent; Put chocolate on top

    I want to see the complete project where a special employee is involved. This include informations about other employees involved in the same project.

    Filtering after "Laurent", should get every Informations about all the projects where Laurent and co-workers worked on.

    The filtered Table could like like this:

    Donut, Laurent, Put chocolate on top

    Donut, Danny, Served the donut

    Donut, Karl, Stamped the hole into the donut

    Cleaning, Laurent, Clean workplace

    Cleaning, Dave, Clean the workplace

    In other words I want to filter an Column based on informations of another column. I could do this on excel but I need it in Power Query.

    Thank you.

    Laurent

    Tuesday, October 16, 2018 7:28 AM

Answers

  • Hey Daniel,

    thank you very much for the detailed explanation. Unfortunately I am really new to theses topics and I did not used the editor since I got you answer.

    Some points may be unclear for me:

    New_Filter = Table.SelectRows(Source, each List.Contains(List_of_Projects, [Project]))

    • New_Filter: This is not a function right? Is this a variable name?
    • Table.SelectRows: Ok, this is the function
    • Source: This is a code for the source. Not a placeholder for the path?
    • List_of_Projects: Is this the name of the list we created before?
    • [Project]: Is this the row I want to filter?

    Well again thank you very much. I will do some efforts to get into the editor way of thinking.

    Hey,

    You can do it all from within the UI. Here's a video that I just recorded showcasing how to do it - sorry for the low quality.

    • Marked as answer by Laurent001 Tuesday, October 23, 2018 9:15 PM
    Tuesday, October 23, 2018 7:22 PM
  • Hey Daniel,

    thank you very much for the detailed explanation. Unfortunately I am really new to theses topics and I did not used the editor since I got you answer.

    Some points may be unclear for me:

    New_Filter = Table.SelectRows(Source, each List.Contains(List_of_Projects, [Project]))

    • New_Filter: This is not a function right? Is this a variable name?
    • Table.SelectRows: Ok, this is the function
    • Source: This is a code for the source. Not a placeholder for the path?
    • List_of_Projects: Is this the name of the list we created before?
    • [Project]: Is this the row I want to filter?

    Well again thank you very much. I will do some efforts to get into the editor way of thinking.

    • Marked as answer by Laurent001 Tuesday, October 23, 2018 9:15 PM
    Tuesday, October 23, 2018 6:02 PM

All replies

  • Hey Laurent!

    Out of curiosity, how would you do that type of filter in Excel? (what's the name of the option inside the filter menu), perhaps it also exists Power Query but it's buried underneath other menus.

    Tuesday, October 16, 2018 1:32 PM
  • Hi Laurent

    It's not difficult, but I think it’s not possible through the GUI, you’ll need the “advanced” text editor and one line of extra code.

    The start point is an Excel table name “Data” with three columns “Project”, “Employee” and “Task”:

    Project    Employee    Task
    A        Laurent        One
    A        Harry        Two
    A        Kevin        Three
    B        Laurent        Other one
    B        Harry        Other two
    B        Kevin        Other three
    C        Harry        Wait
    C        Kevin        See

    Once this table is referenced in Power Query, the first thing to do is filter by the Employee name, “Laurent” in your example.

        Filtered_Rows = Table.SelectRows(Source, each ([Employee] = "Laurent")),

    Then you transform the “Projects” column into a list just referencing the column name:
        
        List_of_Projects = Filtered_Rows[Project],

    These two steps can be done trough the GUI.

    Now you need to apply the list of projects (List_of_Projects) as a new filter but to the original table (“Source”). You’ll have to enter this extra line of code in the advanced (text) editor. Thanks Marcel Beug for the idea.
        
        New_Filter = Table.SelectRows(Source, each List.Contains(List_of_Projects, [Project]))

    The result is this one, a table without the projects in which “Laurent” is not part of.

    Project    Employee    Task
    A        Laurent        One
    A        Harry        Two
    A        Kevin        Three
    B        Laurent        Other one
    B        Harry        Other two
    B        Kevin        Other three

    The full code:

    let
        Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
        Filtered_Rows = Table.SelectRows(Source, each ([Employee] = "Laurent")),
        List_of_Projects = Filtered_Rows[Project],
        New_Filter = Table.SelectRows(Source, each List.Contains(List_of_Projects, [Project]))
    in
        New_Filter

    Have a nice day
    • Proposed as answer by Daniel Herce Wednesday, October 17, 2018 7:52 AM
    Tuesday, October 16, 2018 5:38 PM
  • Hey,

    well I though about creating multiple filter criteria but then I realized that this wont work.

    Actually I have no Idea how to do it not manually.

    Tuesday, October 23, 2018 5:01 PM
  • Hey Daniel,

    thank you very much for the detailed explanation. Unfortunately I am really new to theses topics and I did not used the editor since I got you answer.

    Some points may be unclear for me:

    New_Filter = Table.SelectRows(Source, each List.Contains(List_of_Projects, [Project]))

    • New_Filter: This is not a function right? Is this a variable name?
    • Table.SelectRows: Ok, this is the function
    • Source: This is a code for the source. Not a placeholder for the path?
    • List_of_Projects: Is this the name of the list we created before?
    • [Project]: Is this the row I want to filter?

    Well again thank you very much. I will do some efforts to get into the editor way of thinking.

    • Marked as answer by Laurent001 Tuesday, October 23, 2018 9:15 PM
    Tuesday, October 23, 2018 6:02 PM
  • Hey Daniel,

    thank you very much for the detailed explanation. Unfortunately I am really new to theses topics and I did not used the editor since I got you answer.

    Some points may be unclear for me:

    New_Filter = Table.SelectRows(Source, each List.Contains(List_of_Projects, [Project]))

    • New_Filter: This is not a function right? Is this a variable name?
    • Table.SelectRows: Ok, this is the function
    • Source: This is a code for the source. Not a placeholder for the path?
    • List_of_Projects: Is this the name of the list we created before?
    • [Project]: Is this the row I want to filter?

    Well again thank you very much. I will do some efforts to get into the editor way of thinking.

    Hey,

    You can do it all from within the UI. Here's a video that I just recorded showcasing how to do it - sorry for the low quality.

    • Marked as answer by Laurent001 Tuesday, October 23, 2018 9:15 PM
    Tuesday, October 23, 2018 7:22 PM
  • Hi Laurent

    I'll try to solve some of your doubts:

    New_Filter: This is not a function right? Is this a variable name.

    Yes, "New_Filter" it's just a name of a table generated by a function (Table.SelectRows).

    Source: This is a code for the source. Not a placeholder for the path?

    "Source" it's the name of the first table, the one imported from the Excel file. See the first line after the"let" expression.

    List_of_Projects: Is this the name of the list we created before?

    Yes, when you reference a column "[Project]" from a table "Filtered Rows" you get a list of all the elements of this column. "List_of_Projects" is the name of that list, nothing more.

    [Project]: Is this the row I want to filter?

    No, it's the field of a record. The function "Table.SelecRows" returns every record of the table passed as an argument ("Source" in this case). The second part "each List.Contains(List_of_Projects, [Project])" is another function. This new function gets every record and checks if the value of the field "[Project]" from each record is part of the list "List_Of_Projects". Then the "List.Contains" functions returns a boolean value (true or false) for each record passed by the "Table.SelectRows" function.

    You need to get accustomed to the "M" language, it's not easy, but it's more powerful than the Power Query GUI.

    Tuesday, October 23, 2018 9:01 PM
  • Thank you so much!
    Tuesday, October 23, 2018 9:11 PM
  • Hey Daniel,

    thank you very much for you time and explanations. At my stage I can make a good use of "simple" hints. I already assumed I wont get through the thing just with the GUI.

    Well, time to rush to work :)

    Tuesday, October 23, 2018 9:14 PM