In this article, I will show you how to hide only the summary row after joining multiple Excel books with summary rows in a power query.

If you have not read the following article, please read it first.

How to combine multiple excel books into one using Power Query and split it again using VBA

How to combine multiple excel books into one using Power Query and split it again using VBA

If you are sick and tired of wasting time with cut-and-paste to merge or split excel data, this article is for you.

mago-note

Data for the tutorial

The data used in this article are three Excel books in the folder "Sample4."
Each Excel Book has a sheet named "data", and the summary row is written in the last row.
The figure shows the contents of "01_branchA.xlsx."
02_branchB.xlsx
03_branchC.xlsx

Tutorial to combine Excel books

The combining method is the same as in the previous article, but I write it again here.

Create a new Excel book and click “Get Data” at the left end of the tab “Data”.
Then click “From File” and “From Folder.”
Open the unzipped folder.
Click “Combine & Load” from the pull-down menu of the “Combine” button at the bottom of the window.
Click “data” on the left side of the opened window, and click “OK” on the lower right side.

When the process is completed, the summary row of each Excel book will be imported as shown in the figure.

This summary rows are obstacles for analysis, so let's hide them.

Tutorial to hide summary rows

This section describes how to prevent aggregate rows from appearing in the result of combining.

First, double-click “test4” in the “Queries & Connections” panel.

When the Power Query Editor launches, click the pull-down button in the second column for “No.”

Uncheck “sum” and click the OK button.

Click “Close & Load” in the upper left corner of the screen.

Completed. Summary rows are now hidden.

If you have rows you want to hide, even if they are not summary rows, you can hide them in the same way.