How to remove data from tables in Power BI?

by

Here’s question from a reader: I have a large dataset in Power BI and need to exclude certain records based on specific criteria. How can I remove data from tables using Power Query and from visualizations without affecting the original data source?

Filtering data rows for Power BI tables

Power BI offers multiple ways to remove or filter data, both at the data model level using Power Query and at the visualization level. Let’s explore both approaches to give you full control over your HR data analysis.

Let’s load some data that conforms to this data model: EmployeeData (EmployeeID: Integer, Name: Text, Department: Text, HireDate: Date, Salary: Currency, PerformanceRating: Decimal).

Here’s our data:

Removing Data from Tables in Power Query

  1. Open your Power BI Desktop file and go to the “Home” tab.
  2. Now go ahead, click on “Transform data” to open the Power Query Editor.
  3. In the Queries pane, select the “EmployeeData” table.
  4. Click on the column header dropdown for the column you want to filter (e.g., “Department”).
  5. Choose “Text Filters” (or the appropriate filter type for your column) and select the desired filter option (e.g., “Does not equal”).
  6. Enter the value you want to exclude (e.g., “Finance”) and click “OK”.
  1. To remove multiple values, use the “Advanced filter” option and set up multiple conditions.
  2. For date-based filtering, select the “HireDate” column and choose “Date Filters” to exclude records based on date ranges.
  3. To filter numeric columns like “Salary”, use “Number Filters” and set up conditions (e.g., is less than 50000).
  4. After applying all desired filters, click “Close & Apply” in the Home tab of Power Query Editor.

Note: In the same fashion, you can remove entire columns your dataset using Power Query.

Removing Data from Power BI Visualizations

  1. Open your Power BI report and select the visualization you want to modify.
  2. Use the Filter pane:
    a. If not visible, click the “Filters” button in the Visualizations pane.
    b. Under “Filters on this visual”, add the relevant field (e.g., “Department”).
    c. Choose filter type (Basic/Advanced) and set conditions to exclude data.
  3. Apply slicers:
    a. From the Visualizations pane, select the Slicer visual.
    b. Drag the field you want to filter (e.g., “HireDate”) to the slicer.
    c. Configure the slicer (e.g., set date range) to remove unwanted data points.
  4. Utilize DAX measures:
    a. Go to the Modeling tab and click “New Measure”.
    b. Write a DAX formula to filter data (example below).
    c. Use this measure in your visualizations.
  5. Implement drill-through filters:
    a. Create a new page for detailed views.
    b. Add drill-through filters to exclude certain categories.
    c. Right-click on data points in the main report to drill through to filtered views.
  6. Use the Top N filter:
    a. In the Filters pane, add a numeric field (e.g., “Salary”).
    b. Choose “Top N” filter and set the number of items to display.
  7. Apply cross-filtering and cross-highlighting:
    a. Select a data point in one visualization to filter others.
    b. Hold Ctrl while selecting to choose multiple data points.
  8. Utilize bookmark features:
    a. Apply desired filters to your visualizations.
    b. Go to View > Bookmarks pane > Add bookmark.
    c. Use bookmarks to switch between different data views.

DAX Formula Examples

  1. Filtering in visualizations:
High Performers Count = 
CALCULATE(
    COUNTROWS(EmployeeData),
    EmployeeData[PerformanceRating] > AVERAGE(EmployeeData[PerformanceRating])
)
  1. Creating measures to exclude data:
Active Employees = 
CALCULATE(
    COUNTROWS(EmployeeData),
    EmployeeData[Department] <> "Temporary",
    EmployeeData[HireDate] <= TODAY()
)

Key Concepts

  • Filtering in Power Query affects the data model, reducing the overall dataset size.
  • Using Power Query filters is more efficient than filtering in DAX for large datasets.
  • Filters in visualizations don’t affect the underlying data model.
  • DAX measures provide dynamic filtering capabilities.
  • Slicers and cross-filtering offer interactive data exploration.
  • Bookmarks allow for quick switching between predefined views.

Troubleshooting

Here are a few things to do if you encounter issues with data removal or visualization filters:

  • Verify that your filter conditions are correct and not overly restrictive.
  • Check for any conflicting filters across multiple columns or levels (visual, page, report).
  • Ensure date formats are consistent if filtering by dates.
  • If data isn’t updating after changes, try refreshing your data source (database, Excel, csv etc’).
  • For complex filtering needs, consider using the “Advanced Editor” in Power Query to write custom M code.
  • Ensure that your DAX measures are correctly formatted and reference the right tables/columns.
  • If cross-filtering isn’t working, check your model relationships and bi-directional filtering settings.
  • For performance issues with complex filters, consider using calculated tables or optimizing your data model.