How to hide the summary rows when combining Excel books with Power Query

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.

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

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.

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.