Search code examples
excelexcel-formulapivot-tablefilteringpowerquery

Excel - Exclude an item from Pivot table if a certain value exists in another row

I'm trying to create a list of items which do NOT have a row containing a specific value in it.

Example: I would like to pivot on the below table to show me only a list of the rooms that do NOT have a TV in the Equipment column. If successful, my list would only contain room numbers 102 and 104.

I've tried using the Pivot Table filters, however if I exclude TV from the Equipment column, the list still includes Rooms 101 and 103 due to there being rows with other equipment present. I've tried searching for a solution online however I'm not quite sure how best to word this question..!

Room Number Equipment
101 TV
101 Coffee Machine
101 Kettle
102 Coffee Machine
102 Kettle
103 TV
103 Kettle
103 Coffee Machine
103 Desk Lamp
104 Kettle
104 Desk Lamp

Any guidance would be appreciated.

Solution

  • Using POWER QUERY will be more apt here as it can handle quite a lot amount of datasets, so to achieve this using the said procedure, follow these simple steps using POWER QUERY window UI:

    enter image description here

    • First convert the source ranges into a table and name it accordingly, for this example I have named it as Table_1
    • Next, open a blank query from Data Tab --> Get & Transform Data --> Get Data --> From Other Sources --> Blank Query
    • The above lets the Power Query window opens, now from Home Tab --> Advanced Editor --> And paste the following M-Code by removing whatever you see, and press Done
    let
    Source = Excel.CurrentWorkbook(){[Name="Table_1"]}[Content],
    GroupRows = Table.Group(Source, {"Room Number"}, {{"Count", each _, type table [Room Number=number, Equipment=text]}}),
    Counts = Table.AddColumn(GroupRows, "Custom", each Table.RowCount(Table.SelectRows([Count], each ([Equipment] = "TV")))),
    ExpandColumns = Table.ExpandTableColumn(Counts, "Count", {"Equipment"}, {"Equipment"}),
    FilterBy0 = Table.SelectRows(ExpandColumns, each [Custom] = 0),
    ExcludeUnwantedCols = Table.RemoveColumns(FilterBy0,{"Custom"})
in
    ExcludeUnwantedCols

    enter image description here

    • Lastly, to import it back to Excel --> Click on Close & Load or Close & Load To --> The first one which clicked shall create a New Sheet with the required output while the latter will prompt a window asking you where to place the result.

    Alternatively, as commented above using Excel Formulas I would have opted using FILTER() and COUNTIFS() Functions respectively.

    enter image description here

    =FILTER(A2:B12,COUNTIFS(A2:A12,A2:A12,B2:B12,"TV")=0)