Power Query Problems

Flatten Regional Worksheet Sections into an Excel Table with Power Query

Ayan MasoodAyan Masood Nov 18, 2026 869 views

Question details

Combine regional sections separated by blank rows and repeated headers into one structured table for Power BI.

Flatten Regional Worksheet Sections into an Excel Table with Power Query
Product
Microsoft Excel Power Query and Power BI
Device & OS
not provided
Scenario
A worksheet contains multiple regional data sections that need to be combined for reporting.
Observed behavior
The goal is one table with consistent column headers, regional values on each data row, and no blank rows or repeated headers.
Before you start

Check that the regional sections use the same columns in the same order, and identify where each region name appears. Note the workbook location and the header text so you can select and filter the correct rows.

Solution 1Recommended

Clean and combine the worksheet sections with Power Query

Use this method to turn similarly structured regional sections into one refreshable table.

The exact filtering steps depend on whether region names appear in their own column or as standalone section labels. Preserve those labels until you have filled the region value into the data rows.

1
Connect to the worksheet

In Excel, select Data > Get Data > From File > From Workbook. Choose the workbook, select the worksheet in Navigator, and click Transform Data. In Power BI Desktop, use Home > Get Data > Excel workbook, then select the worksheet and click Transform Data.

2
Check the headers

In Power Query Editor, inspect the preview and Applied Steps. If an automatic Promoted Headers step used a region label or the wrong row as headers, select the X next to that step. Use Home > Use First Row as Headers only when the first row contains the actual column names; otherwise, rename the columns manually so section labels are retained.

3
Remove empty rows

Select Home > Remove Rows > Remove Blank Rows to remove fully empty spacing rows between sections.

4
Fill the region values

If region names already appear in a region column, select that column and choose Transform > Fill > Down. If they appear as standalone labels, use Add Column > Conditional Column to create a region column that returns each known region name for its label row and null otherwise; then select the new column and choose Transform > Fill > Down.

5
Filter section labels and repeated headers

Open the filter menu on the column containing the section labels and repeated header text. Exclude those label and header values after filling the region column, leaving only data rows. Check the preview to ensure no valid records were removed.

6
Set types and load

Select each column and set its type using Transform > Data Type. In Excel, choose Home > Close & Load to load the result; in Power BI Desktop, choose Home > Close & Apply.

Clean and combine the worksheet sections with Power Query
When the workbook moves: Update the workbook path in Data Source Settings or edit the query's Source step, then refresh the query. The new file should have the same worksheet and column layout.
Free Microsoft Office alternative

Review Regional Worksheets with WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and Excel format compatibility, making it easy to open and work with existing workbooks. Use Microsoft Power Query for the refreshable transformation described above.

Review regional worksheet sections in a familiar spreadsheet interfaceOpen and edit Microsoft Excel XLS and XLSX filesMove existing workbooks into a lightweight office suiteUse free spreadsheet tools for everyday editing
QA img-9

Frequently Asked Questions

How do I remove repeated headers without losing the first header row?

In Power Query Editor, first use the correct row as column headers or rename the columns manually. Then use the relevant column's filter menu to exclude the repeated header text from the remaining rows.

How do I keep the region name on every data row?

Select the region column in Power Query Editor and choose Transform > Fill > Down. If the region is a standalone label, first create a conditional column that returns the region name on label rows and null on other rows, then fill it down.

What should I do if the workbook location changes?

Open Data Source Settings and change the workbook path, or edit the Source step in Power Query Editor. Refresh the query after confirming that the new workbook has the expected worksheet and layout.

Can I load the cleaned data directly into Power BI?

Yes. In Power BI Desktop, use Home > Get Data > Excel workbook, select the worksheet, and choose Transform Data. After cleaning the data in Power Query Editor, select Home > Close & Apply.