How to extract only multiple tables from same work sheet in MS Excel using power Query?

Question

Powerquery can extract tables from an MS Excel worksheet easily. I require to extract multiple tables from the same worksheet. The worksheet also has values stored outside the tables(ex:- avg, etc). I don't require those values. I only need to extract the tables.

I have tried using the generic read from workbook method but it does not work out. It takes all the values and makes a bigger table filling empty spaces with blanks. I would manually need to delete all the unrequired rows.

I want multiple tables to show up when a single worksheet as multiple tables. Instead, I get the whole worksheet area.

Answer 1

Select the area your data is in and click on "From Table/Range" under the data ribbon.

Do this for every table in the same worksheet.

This should only include the data in power query editor that you have selected.

Answer 2

You should import your file by Import Folder. This way of import get you the power of handling the growing files and worksheets of a file or Tables.

Or if you use M language for getting data, you could use Excel.Workbook() and after that filter Tables only.