How to remove data from tables in Power BI?

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”.

7. To remove multiple values, use the “Advanced filter” option and set up multiple conditions.
8. For date-based filtering, select the “HireDate” column and choose “Date Filters” to exclude records based on date ranges.
9. To filter numeric columns like “Salary”, use “Number Filters” and set up conditions (e.g., is less than 50000).
10. 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])
)

2. 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.

Remove Power BI data without breaking refresh logic

The safest approach is to define the intended result before changing settings. Identify whether rows should be filtered at the source, in Power Query, or only hidden from a visual. Keep the current file, profile, or configuration available so recovery remains possible.

Compare one condition at a time

Preserve the original query or create a reference before destructive transformations. Then check downstream relationships, measures, and calculated tables. A controlled comparison is more useful than a reinstall because it identifies whether the boundary is the account, data, client, device, or service.

Protect data and permissions

Compare row counts and refresh duration before and after the change. Do not use visual filters as a security boundary. If a setting is locked or labeled as organization-managed, capture the message and ask the responsible administrator instead of bypassing policy.

Confirm the outcome

Publish to a test workspace before replacing a shared model. Close and reopen the application, then repeat the ordinary workflow with a noncritical sample. Check synchronization or scheduled refresh when timing affects the feature.

For escalation, record the exact action, result, app version, identity type, and time. Share only sanitized screenshots. A second authorized user’s result can reveal hidden owner privileges or stale state. Undo experiments that did not help and retain the known-good configuration until the fix works consistently. Recheck the result after the next sign-in or scheduled cycle when timing can affect it. Keep a concise note of the successful condition so it can be restored later. Verify that the change did not alter unrelated files, permissions, or settings. Recheck the result after the next sign-in or scheduled cycle when timing can affect it. Keep a concise note of the successful condition so it can be restored later. Verify that the change did not alter unrelated files, permissions, or settings.

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.