Power Query Problems

How to Show or Hide Date Columns Based on Related Data in Excel Power Query

Anum Mushtaq Oct 23, 2026 869 views

Question details

The user needs to hide specific date columns in Power Query when their corresponding related data values are null or blank.

How to Show or Hide Date Columns Based on Related Data in Excel Power Query

Product: Excel
Device & OS: not provided
Scenario: Transforming and cleaning data in Power Query where certain date columns (such as Date6 through Date10) should only appear if related quantities or pipelines exist.
Observed behavior: Date columns continue to display in the Power Query output even when the related Qty or Pipeline values are null or blank.

Before you start

Ensure you are familiar with the Power Query Editor interface in Excel and understand the difference between 'null' values and blank text strings in your dataset.

Solution 1Recommended

Apply Conditional Column Logic to Filter Null Values

Use the Conditional Column feature to check for null or blank values in your related data before displaying the date.

By setting up conditional logic, you can instruct Power Query to evaluate the 'Qty' or 'Pipeline' column first. If the value is missing, the corresponding date field will also be set to null, which cleans up the final output.

Open Power Query Editor

Select your data table in Excel, navigate to the 'Data' tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.

Add a Conditional Column

Go to the 'Add Column' tab at the top of the editor and click on 'Conditional Column'.

Configure the Logic

In the dialog box, set the 'Column Name' to your related data (e.g., Qty). Set the 'Operator' to 'equals' and type 'null' in the 'Value' field.

Set the Output

For the 'Output', choose to return 'null'. For the 'Otherwise' condition, choose 'Select a column' and pick your target Date column (e.g., Date6).

Clean Up and Load

Remove the original, unfiltered date columns by right-clicking their headers and selecting 'Remove'. Finally, click 'Close & Load' on the Home tab to apply the changes to your Excel workbook.

Apply Conditional Column Logic to Filter Null Values

Check for Blanks: If your cells contain empty strings instead of pure nulls, add an additional 'Else If' clause in your conditional column to check if the value equals an empty field.

Solution 2

Filter Nulls Before Reshaping Data (Unpivot Method)

Unpivot your date columns, filter out the missing values globally, and then pivot the data back if a wide format is required.

Filter and Hide Data Dynamically in WPS Spreadsheet

You can achieve similar conditional data visibility directly in WPS Spreadsheet using standard IF formulas and filtering features, skipping the complexity of Power Query altogether.

1. Open Your Dataset in WPS Spreadsheet: Launch WPS Office and open your Excel workbook containing the raw data.
2. Apply IF Formulas: Create a new column using a formula like =IF(ISBLANK(B2), "", C2), assuming B is your Qty column and C is your Date column.
3. Copy the Logic: Drag the fill handle down to apply this logic to all rows, effectively hiding dates where the related data is blank.
4. Filter the Results: Select your new column headers, go to the 'Data' tab, and click 'Filter' to easily hide any rows that generated blank results.

100% compatible with Microsoft Excel formats (.xlsx, .xls, .csv).Simple IF formula logic for conditional data display.Built-in Advanced Filter for effortless data management.Lightweight application that runs smoothly on low-spec devices.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Power Query show null values instead of hiding the column?

Power Query processes data in a structured, tabular format. By default, it populates missing intersections with 'null' rather than hiding columns dynamically. You must apply specific filtering or conditional column steps to control what is displayed.

What is the difference between null and blank in Power Query?

In Power Query, 'null' represents the complete absence of a value, whereas a blank (or empty string) is a recognized text value containing zero characters. Data cleaning often requires checking for both conditions.

Can I hide multiple date columns at once based on one condition?

Yes, you can achieve this by using the 'Unpivot Columns' feature to flatten your data, applying a single filter to remove the null or blank rows in your target column, and then reshaping the data.

Excel & Data Problems