Reducing Data Volume and Removing Unnecessary Columns in Power BI

Course: Microsoft Power BI Training

Topic: Reducing Data Volume and Removing Unnecessary Columns

Course Link: Microsoft Power BI Training – JustAcademy

Demo Link: Register for a Free Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to Reducing Data Volume in Power BI

Microsoft Power BI is a business intelligence and data visualization platform used to transform raw data into meaningful reports, dashboards, and analytical insights. Before creating reports, it is important to prepare the data properly. One of the most important data preparation activities is reducing data volume and removing unnecessary columns.

Business datasets often contain thousands or millions of rows and many columns that are not required for reporting. Loading all available data without evaluating its usefulness can increase file size, consume memory, slow down data refreshes, and make data models more complicated.

Power BI provides Power Query Editor, filtering options, data transformation features, and data modeling capabilities that help users keep only the information needed for analysis.

Why Is Data Reduction Important?

• Improves report and model performance.
• Reduces the amount of memory required to store data.
• Can reduce the size of Power BI Desktop files and semantic models.
• Helps improve data refresh efficiency.
• Removes irrelevant information from the analytical model.
• Makes tables easier to understand and maintain.
• Reduces unnecessary complexity in relationships and calculations.
• Supports better data governance by limiting unnecessary data collection.

2. Understanding Data Volume in Power BI

Data volume refers to the quantity of data that Power BI imports, processes, stores, or queries. It is influenced by the number of rows, the number of columns, the types of data stored, the uniqueness of values, and the storage mode used by the model.

2.1 Rows and Columns

A table contains rows and columns. Each row generally represents a record, such as an order, customer, product, or transaction. Each column represents an attribute of that record, such as order date, customer name, quantity, price, or region.

For example, a sales dataset might contain these columns:

If the report analyzes sales by date, product, and region, columns unrelated to these requirements may be unnecessary. However, a column should not be removed merely because it is not displayed directly in a visual. It may still be needed for relationships, filtering, sorting, security, or calculations.

2.2 What Happens When Too Much Data Is Loaded?

• Increased memory usage: Imported data must be processed and stored according to the model's storage requirements.
• Larger semantic model: Additional data can increase the amount of storage required.
• Slower refreshes: More data may require additional time to extract, transform, and load.
• More complex modeling: Unnecessary fields can make the Fields pane difficult to navigate.
• Potentially slower queries: Large models and inefficient transformations may increase query processing costs.

3. Understanding Unnecessary Columns

An unnecessary column is a field that does not contribute to the required reporting, analysis, relationships, business rules, or data management requirements. Identifying these columns is an important step in building an efficient Power BI data model.

3.1 Common Types of Unnecessary Columns

3.2 Columns That Should Not Be Removed Without Checking

Some columns may appear unnecessary but are essential to the model. Before deleting them, verify whether they are used by:

• Relationships between fact and dimension tables.
• Calculated columns, measures, or Power Query expressions.
• Sorting rules, such as month names sorted by month number.
• Row-level security rules.
• Drill-through pages, slicers, filters, or tooltips.
• Data quality checks, reconciliation, or auditing.
• Incremental refresh configurations or source-system requirements.

Best practice: Confirm the purpose of each column with the report owner or business team before removing it from a shared production model.

4. Introduction to Power Query Editor

Power Query Editor is the data preparation environment in Power BI Desktop. It allows users to connect to data sources, clean data, filter rows, remove columns, change data types, split fields, merge tables, and perform other transformations before data is loaded into the model.

4.1 Opening Power Query Editor

1. Open Power BI Desktop.
2. Open an existing report or create a new report.
3. Select the Home tab.
4. Choose Transform data.
5. Power Query Editor opens with the available queries listed in the Queries pane.
6. Select the table or query that you want to optimize.

4.2 Important Areas of Power Query Editor

5. Removing Unnecessary Columns in Power Query

Removing unnecessary columns is one of the simplest ways to streamline data preparation. It helps keep the final table focused on information required for analysis.

5.1 Method 1: Select and Remove Specific Columns

1. Open Power Query Editor.
2. Select the required query.
3. Identify the columns that are not needed.
4. Hold Ctrl while selecting multiple non-adjacent column headers, if required.
5. Right-click a selected column header.
6. Select Remove Columns.
7. Review the data preview to confirm that the correct columns were removed.
8. Select Close & Apply to apply the transformations and load the results.

This method is suitable when only a small number of columns need to be removed from a table.

5.2 Method 2: Remove Other Columns

The Remove Other Columns command keeps only the selected columns and removes all other columns. This is useful when a source table contains many fields but the report needs only a few.

1. Open the required query.
2. Select the columns that must remain in the final dataset.
3. Right-click one of the selected column headers.
4. Choose Remove Other Columns.
5. Verify that all required fields remain.
6. Apply the transformation.

For example, if a table contains 35 columns and the report needs only 8, selecting those 8 columns and using Remove Other Columns provides a quick way to create a focused table.

Important: Remove Other Columns can be sensitive to schema changes. If a new column is added to the source later, it may not automatically appear in the final query because the transformation explicitly retains a defined set of columns.

5.3 Method 3: Remove Columns Using the Ribbon

1. Select the column or columns to remove.
2. Open the Home or Transform tab, depending on the available ribbon layout.
3. Choose the column removal command.
4. Check the preview and Applied Steps pane.

5.4 Method 4: Remove Columns Using the M Language

Power Query transformations are expressed using the Power Query M language. The Table.RemoveColumns function removes the specified columns from a table.

Example dataset:

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    RemovedColumns = Table.RemoveColumns(
        Source,
        {"InternalComment", "TemporaryImportFlag", "UnusedReferenceCode"}
    )
in
    RemovedColumns

In this example:

• Source represents the original data table.
• Table.RemoveColumns removes the listed fields.
• RemovedColumns represents the transformed table.
• The in statement returns the final result.

The example assumes that the source is an Excel table named SalesData and that the specified columns exist.

5.5 Handling Columns That May Not Exist

When a source schema changes, a specified column might be missing. By default, attempting to remove a nonexistent column can cause an error. If the intended behavior is to ignore missing columns, use the optional missing-field argument.

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    RemovedColumns = Table.RemoveColumns(
        Source,
        {"InternalComment", "UnusedReferenceCode"},
        MissingField.Ignore
    )
in
    RemovedColumns

MissingField.Ignore allows the query to continue when one or more specified columns are absent. Use it only when ignoring those missing fields is consistent with the business requirements.

6. Reducing the Number of Rows

Reducing data volume is not limited to removing columns. It can also involve filtering rows so that the model imports only the records needed for analysis.

6.1 Filter Rows by Date

Suppose a sales table contains ten years of transactions, but the report only needs the previous three completed years. A date filter can reduce the number of records loaded.

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    ChangedType = Table.TransformColumnTypes(
        Source,
        {{"OrderDate", type date}}
    ),
    FilteredRows = Table.SelectRows(
        ChangedType,
        each [OrderDate] >= #date(2023, 1, 1)
            and [OrderDate] < #date(2026, 1, 1)
    )
in
    FilteredRows

This example keeps records from January 1, 2023, up to but not including January 1, 2026. Replace the dates with the reporting period required by your project. A fixed date range will not automatically move forward unless the query is designed to use dynamic dates.

6.2 Filter Rows by Business Conditions

You can retain only records that meet specific business conditions, such as completed orders or transactions with a positive sales amount.

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    FilteredRows = Table.SelectRows(
        Source,
        each [Status] = "Completed"
            and [SalesAmount] > 0
    )
in
    FilteredRows

Filtering must follow the intended business definition. For example, negative sales amounts might represent legitimate refunds, so excluding them without validation could produce inaccurate revenue figures.

6.3 Remove Blank Rows and Unnecessary Records

• Remove fully blank rows when they are not meaningful records.
• Filter out test transactions if they are explicitly excluded from reporting.
• Remove duplicate records only after identifying the correct business key and duplicate definition.
• Exclude historical records only when the required reporting period permits it.

7. Understanding Query Folding

Query folding occurs when Power Query translates supported transformation steps into operations that the underlying data source can execute. For example, a filter or column selection may be translated into a SQL query instead of requiring Power Query to retrieve every column and row first.

7.1 Why Query Folding Matters

• It can reduce the amount of data transferred from the source.
• It can move filtering and transformation work to a database that is optimized for these operations.
• It can improve refresh performance for supported data sources.
• It may reduce the processing burden on Power BI during data ingestion.

7.2 Example of a SQL Source

Suppose the original source table contains many columns, but the report needs only four. A folding-capable connector may translate the Power Query steps into SQL similar to the following:

SELECT
    OrderDate,
    ProductID,
    Quantity,
    SalesAmount
FROM dbo.Sales
WHERE OrderDate >= '2023-01-01';

This SQL illustrates the type of source-side operation that may result from query folding. Power Query does not necessarily generate this exact SQL for every connector or transformation.

7.3 Best Practices for Query Folding

• Remove unnecessary columns early in the query where practical.
• Apply selective filters early, provided the transformation order preserves the intended results.
• Prefer supported source-side operations for large relational datasets.
• Check the available query folding indicators or native query options where supported.
• Be aware that certain transformations, custom functions, and connector limitations can stop folding.
• Test refresh performance using representative production data.

Query folding depends on the connector, source, and transformation sequence. It should be verified rather than assumed.

8. Choosing the Correct Columns for a Data Model

Before removing columns, identify what the report needs to measure, compare, filter, group, and explain. A well-designed model includes the fields needed for these tasks without carrying irrelevant data into the final model.

8.1 Analyze Business Requirements

1. Identify the purpose of the report.
2. List the metrics that must be calculated.
3. Identify dimensions such as date, product, customer, region, and category.
4. Determine the fields required for relationships between tables.
5. Review fields needed for filters, sorting, security, and drill-through.
6. Identify columns that do not contribute to the required analysis.
7. Confirm the proposed removals with stakeholders.

8.2 Example: Sales Reporting Dataset

9. Reducing Data Volume in Different Power BI Storage Modes

Power BI supports different ways of accessing and storing data. The best data-reduction approach depends partly on the storage mode.

9.1 Import Mode

In Import mode, data is loaded into the Power BI semantic model. Removing unused columns and filtering unnecessary rows before loading can directly reduce the amount of data stored in the imported model.

Recommended practices:

• Import only the columns required for analysis.
• Filter out irrelevant rows when appropriate.
• Use appropriate data types.
• Reduce unnecessary text and high-cardinality fields.
• Evaluate aggregation tables or incremental refresh for suitable large datasets.

9.2 DirectQuery Mode

In DirectQuery mode, Power BI generally sends queries to the underlying data source instead of importing the full detail data into the model. Removing unnecessary fields from the model and visuals can simplify queries, but performance also depends on the source database, query design, indexes, network, and transformations supported by the connector.

Recommended practices:

• Keep the model focused on the fields required for reporting.
• Optimize source database views and queries where appropriate.
• Avoid unnecessarily complex visuals and interactions.
• Use source-side filtering and appropriate indexing where possible.
• Test performance under realistic usage conditions.

9.3 Direct Lake and Composite Models

Direct Lake and composite models have different data-access and storage behaviors. Model optimization must account for the underlying storage architecture, table design, relationships, and the balance between imported and source-accessed data. Removing unused fields remains useful when those fields are not needed by the model, but the performance effect depends on the specific implementation.

10. Data Types and Their Effect on Model Size

Correct data types improve data quality and can influence storage efficiency, calculations, and query performance. A number should not be stored as text unless text is required by its meaning or format.

Postal codes and product codes often need to remain text because they can contain leading zeros or nonnumeric characters. Choosing a numeric type simply because a field contains digits can corrupt its meaning.

10.1 Change Data Types in Power Query

1. Select the column in Power Query Editor.
2. Use the data type selector in the column header or ribbon.
3. Choose the correct data type.
4. Check for conversion errors.
5. Validate sample values and downstream calculations.

10.2 M Code Example

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    ChangedTypes = Table.TransformColumnTypes(
        Source,
        {
            {"OrderDate", type date},
            {"Quantity", Int64.Type},
            {"SalesAmount", Currency.Type},
            {"CustomerName", type text}
        }
    )
in
    ChangedTypes

Use a type that matches the business meaning and required precision of each column. Incorrect conversion can cause errors or misleading results.

11. Removing Duplicate and Redundant Information

Repeated data can increase model complexity and create inconsistencies. However, duplicate values in a column are not automatically a problem. For example, many sales transactions may legitimately contain the same customer ID.

11.1 Identify Redundancy

• Look for fields that repeat information already available through a dimension table.
• Check whether duplicate descriptive fields are needed for a specific reporting requirement.
• Determine whether the repeated information is a legitimate transaction attribute.
• Check whether a field is necessary for relationships or traceability.

11.2 Example of a Better Data Model

Instead of repeating all product descriptions in a large sales table, a model can store transaction-level values in a Sales fact table and product attributes in a Products dimension table.

The tables can be related through ProductID when the key is appropriate and unique on the dimension side. This star-schema approach can improve model organization and reduce repeated descriptive data. It must be designed carefully to preserve the required business logic.

12. Understanding Applied Steps After Removing Columns

Power Query records transformations in the Applied Steps pane. Each step represents an operation performed on the query, such as selecting a source, changing data types, filtering rows, or removing columns.

12.1 Example of Applied Steps

1. Source
2. Navigation
3. Promoted Headers
4. Changed Type
5. Removed Columns
6. Filtered Rows

The exact steps depend on the data source and transformations performed. You can select a step to inspect the data preview at that point in the process.

12.2 Why Step Order Matters

Transformation order can affect results and query folding. For example, removing a column before a later step that references it can cause an error. Similarly, filtering or grouping data at the wrong point can change the business meaning of the final result.

• Review dependent steps before deleting or moving a transformation.
• Confirm that every referenced column exists at the step where it is used.
• Test the final output after changing step order.
• Check whether the changes affect query folding or refresh performance.

13. Complete Practical Example

In this example, a company has a sales dataset with unnecessary columns, incorrect data types, and records outside the required reporting period. The goal is to prepare a smaller, report-ready table.

13.1 Original Dataset

13.2 Business Requirements

• Keep OrderID, OrderDate, ProductID, Quantity, and SalesAmount.
• Remove InternalComment and TemporaryFlag.
• Keep orders from January 1, 2023, onward.
• Ensure that the date, quantity, and sales amount have suitable data types.

13.3 Power Query M Code

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    ChangedTypes = Table.TransformColumnTypes(
        Source,
        {
            {"OrderID", Int64.Type},
            {"OrderDate", type date},
            {"ProductID", type text},
            {"Quantity", Int64.Type},
            {"SalesAmount", Currency.Type},
            {"InternalComment", type text},
            {"TemporaryFlag", type text}
        }
    ),
    FilteredRows = Table.SelectRows(
        ChangedTypes,
        each [OrderDate] >= #date(2023, 1, 1)
    ),
    RemovedColumns = Table.RemoveColumns(
        FilteredRows,
        {"InternalComment", "TemporaryFlag"}
    ),
    SelectedColumns = Table.SelectColumns(
        RemovedColumns,
        {
            "OrderID",
            "OrderDate",
            "ProductID",
            "Quantity",
            "SalesAmount"
        }
    )
in
    SelectedColumns

This example assumes that the source table is named SalesData and that all referenced columns exist. In a real project, adjust the types, filter conditions, and retained fields to match the source data and reporting requirements.

13.4 Expected Result

The 2022 record is excluded by the date filter, and the two unnecessary columns are removed. The final table contains only the five fields required for the report.

14. Common Errors and Troubleshooting

15. Best Practices for Reducing Data Volume

• Understand requirements first: Define the business questions before removing data.
• Keep only useful fields: Remove columns that have no reporting, modeling, security, or governance purpose.
• Filter carefully: Exclude rows only when the business rules permit it.
• Transform early where practical: Reduce data before loading it into an Import model.
• Preserve necessary keys: Retain identifiers required for relationships and traceability.
• Use suitable data types: Avoid storing numeric or date values as text when a proper type is appropriate.
• Check query folding: For supported sources, verify that useful operations are executed at the source.
• Avoid unnecessary duplication: Use an appropriate model design to separate transaction facts from descriptive dimensions.
• Validate totals: Compare row counts, key metrics, and important categories before and after transformations.
• Test refreshes: Confirm that the query works with current source data and expected schema changes.
• Document important decisions: Record why major filters and column removals were introduced.
• Protect sensitive data: Do not import fields that are unnecessary for the report and should not be exposed in the model.

16. Measuring the Results of Data Reduction

After removing unnecessary data, evaluate whether the changes produced the intended benefits.

16.1 Useful Measurements

• Number of rows before and after filtering.
• Number of columns before and after removal.
• Model size before and after changes.
• Data refresh duration.
• Query performance for important report pages.
• Validation of totals and key business metrics.

16.2 Example Comparison

The figures above illustrate a hypothetical scenario, not guaranteed performance improvements. Actual results depend on the data, column types, source system, model design, and transformations.

17. Hands-On Exercises

Exercise 1: Remove Unnecessary Columns

Import an Excel or CSV file containing at least ten columns. Identify five fields that are not needed for the intended report and remove them using Power Query Editor.

Tasks:

1. Open Power Query Editor.
2. Identify the columns required for analysis.
3. Remove the unnecessary columns.
4. Inspect the Applied Steps pane.
5. Apply the changes and verify the final table.

Exercise 2: Use Remove Other Columns

Choose four columns that must remain in a dataset. Use Remove Other Columns to retain only those fields.

Expected learning: Understand how selecting the columns to retain differs from selecting the columns to remove.

Exercise 3: Filter Rows

Filter a transaction dataset to include only records from a specified date range. Compare the row count and total sales before and after filtering.

Expected learning: Understand the effect of row filters on dataset size and business calculations.

Exercise 4: Write Power Query M Code

Create a query that removes the columns Notes and TemporaryCode and retains only OrderDate, ProductID, and SalesAmount.

let
    Source = Excel.CurrentWorkbook(){[Name="SalesData"]}[Content],
    RemovedColumns = Table.RemoveColumns(
        Source,
        {"Notes", "TemporaryCode"}
    ),
    SelectedColumns = Table.SelectColumns(
        RemovedColumns,
        {"OrderDate", "ProductID", "SalesAmount"}
    )
in
    SelectedColumns

Run this example only when the source table and referenced columns exist. Alternatively, remove the unnecessary columns directly through the Power Query interface.

18. Mini Project: Optimize a Sales Dataset

Project Objective: Prepare a large sales dataset for reporting by removing unnecessary fields, filtering irrelevant transactions, and applying appropriate data types.

Project Requirements

• Load a sales dataset from Excel, CSV, or a database.
• Inspect all available columns.
• Identify columns needed for sales, product, customer, and time analysis.
• Remove unused operational or temporary fields.
• Filter records according to approved reporting requirements.
• Set appropriate data types.
• Check for transformation errors.
• Compare source and final row counts.
• Validate sales totals and other important metrics.
• Create a simple Power BI report using the optimized dataset.

Expected Deliverables

1. A cleaned Power Query query.
2. A documented list of removed columns and filtered records.
3. A validation summary comparing the original and final datasets.
4. A Power BI report with sales KPIs and relevant charts.
5. A short explanation of the performance and maintenance benefits observed.

19. Interview Questions and Answers

Q1. What is data reduction in Power BI?

Answer: Data reduction is the process of limiting the rows, columns, and unnecessary information loaded or processed by Power BI so that the model contains the data required for analysis.

Q2. Why should unnecessary columns be removed?

Answer: Removing unnecessary columns can reduce model size, memory usage, and data processing requirements while making the model easier to maintain.

Q3. What is Power Query Editor?

Answer: Power Query Editor is the environment used to connect to data sources and transform data before loading it into the Power BI model.

Q4. What is the difference between Remove Columns and Remove Other Columns?

Answer: Remove Columns deletes the selected fields. Remove Other Columns keeps the selected fields and removes all remaining columns.

Q5. What is the Power Query M function for removing columns?

Answer: Table.RemoveColumns removes specified columns from a table.

Q6. What is query folding?

Answer: Query folding is the process by which Power Query translates supported transformation steps into operations that the underlying data source can execute.

Q7. Can removing columns improve refresh performance?

Answer: Yes, it can, particularly when fewer columns need to be retrieved, transferred, transformed, and loaded. The actual improvement depends on the connector and source system.

Q8. Should every column not shown in a report be removed?

Answer: No. A column may still be needed for relationships, measures, filters, sorting, security, or future approved requirements.

Q9. What is the difference between row filtering and column removal?

Answer: Row filtering controls which records are retained, while column removal controls which fields are retained for each record.

Q10. How can you confirm that data reduction worked?

Answer: Compare row and column counts, model size, refresh duration, and important business totals before and after the transformations.

Q11. What happens if a removed column is used in a later transformation?

Answer: The query may return an error because a later step references a field that no longer exists. Review the Applied Steps pane and correct the dependency.

Q12. Why are data types important?

Answer: Correct data types preserve meaning, support accurate calculations, and can improve model efficiency.

20. Quick Revision Checklist

• Understand the business reporting requirements before modifying data.
• Identify unused, temporary, duplicate, and irrelevant columns.
• Keep keys required for relationships and business calculations.
• Use Remove Columns or Remove Other Columns appropriately.
• Filter rows only according to validated business rules.
• Use suitable data types for dates, numbers, text, and Boolean values.
• Review Applied Steps after making transformations.
• Check query folding where supported.
• Validate row counts and important business totals.
• Compare model size and refresh performance before and after optimization.
• Test refreshes when source schemas change.

21. Conclusion

Reducing data volume and removing unnecessary columns are essential data preparation practices in Microsoft Power BI. By retaining only the fields and records needed for analysis, users can create cleaner models, reduce unnecessary processing, and improve the maintainability of reports.

Power Query Editor provides convenient tools such as Remove Columns, Remove Other Columns, row filters, data type transformations, and the Power Query M language. When combined with appropriate model design and query folding where supported, these techniques can help improve data preparation efficiency.

The most important principle is to optimize data without losing business meaning. Always validate relationships, calculations, record counts, and key metrics before publishing the optimized model.

Continue Learning: Explore the Microsoft Power BI Training course and register for a course demo to continue learning data transformation, modeling, and reporting techniques.