How to Shift Values Left for Specific Empty Cells in Power Query
Question details
The user needs to shift values to the left in Power Query to fill empty cells, but only within a specific column range (columns 3 through 6).
- Product
- Power Query / Excel
- Device & OS
- not provided
- Scenario
- Data transformation requires removing gaps in specific columns to consolidate data without affecting the structure or intended empty cells of adjacent columns.
- Observed behavior
- Empty cells exist between results, and applying a global left-shift affects columns outside the target range, disrupting the overall data structure.
Before modifying your data transformation steps, confirm the exact index or names of the columns you want to target (e.g., columns 3 through 6) to ensure surrounding data remains untouched.
Merge and Split Columns to Shift Target Values Left
Use the merge and split technique on the specific column range to eliminate blank values and force the remaining data to shift left, leaving outside columns unaffected.
Power Query does not have a native 'Shift Cells Left' button. To bypass this, you can merge the target columns using a unique delimiter, clean out the consecutive delimiters caused by empty cells, and then split the columns back apart.
Open the Power Query Editor. Hold the Ctrl key and click the headers of the specific columns you want to modify (e.g., Column3, Column4, Column5, and Column6).
Right-click the selected headers and choose 'Merge Columns'. Select a custom delimiter that does not appear in your data (like a semicolon ';' or a pipe '|') and click OK.
Right-click the newly merged column and select 'Replace Values'. If you used a semicolon, replace ';;' with ';' to remove the gap left by the empty cells. Repeat this replacement if there are multiple consecutive blanks.
Right-click the merged column, select 'Split Column', and choose 'By Delimiter'. Use the same custom delimiter and ensure it splits into the original number of columns. The data will now be shifted left within this range.
Double-click the new column headers to rename them back to their original names, ensuring your data structure is properly restored.
Try WPS Office for Seamless Spreadsheet Management
While highly complex Power Query transformations require Microsoft Excel, WPS Office provides a lightweight, highly compatible alternative for everyday data analysis, filtering, and spreadsheet tasks without the hefty subscription fees.
- 1. Download WPS Office: Visit the official WPS website to download and install the free WPS Office suite.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing Excel workbook to immediately access your data.
- 3. Manage Data Easily: Navigate to the Data tab to utilize powerful sorting, filtering, and data validation tools as a seamless alternative.
Frequently Asked Questions
Can I shift cells left without using Power Query?
Yes, in a standard spreadsheet environment, you can select the blank cells, right-click, choose 'Delete', and then select 'Shift cells left'. However, this affects the spreadsheet grid directly, whereas Power Query handles this automatically during data refresh.
Why do empty cells appear as 'null' in Power Query?
In Power Query, 'null' represents the complete absence of a value. Removing or replacing these 'null' values is necessary when cleaning data or preparing columns to shift.
Is there an M code function to shift cells left?
There is no single built-in M code function called 'ShiftLeft'. Advanced users often write custom M code using List.RemoveNulls on a row-by-row basis (via Table.AddColumn) to filter out blanks before converting the lists back into columns.
