Flatten Regional Worksheet Sections into an Excel Table with Power Query
Question details
Combine regional sections separated by blank rows and repeated headers into one structured table for Power BI.

- 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.
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.
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.
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.
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.
Select Home > Remove Rows > Remove Blank Rows to remove fully empty spacing rows between sections.
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.
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.
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.

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.

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.




