Importing Excel empty lines in a sheet - How can I import only data content?

[Anonymous]

Hi,

I'm trying to import a hole file that has many Excel workbooks. If I count all the summed lines of all worksheets imported, it was supposed to have, for instance, 30.000 lines, however, for some reason the power bi is importing even the empty lines, so my power bi table has 250.000 lines instead of 30.000, and most of them are blank, expect for the first column that contain the information of directory source. 

How can I import only the lines that has contents? 

[watkinnc · Accepted solution]

Before you expand the table column, let's  assume that Column2 in these tables is good column on which to remove nulls.  You could probably use:

FilterNulls = Table.SelectRows(PreviousStepName, each [TableColumnName][Column2] <> null)

Now when you expand the table column, the nulls should be filtered out.
--Nate

[edhans]

Your file has to be set up correctly. You have a few options:

1. Make sure the worksheet only has data where you want it. I just created a workbook with a small set of data in A1:A5, then I put some text in cell GG1000. Power Query pulled in all 1,000 rows and 189 columns. Remove all data you don't need. That includes formatting.
2. If you only want specific data, put it in Excel Tables, not "grids of data." Power Query will connect and see a table in cells A1:K100 and only pull that data regardless of whatever else is in the file.
3. Turn on the preview feature called Excel table inference. It will scan the Excel file and look for what it thinks are tables. You'll see this when importing:

The best solution is #2 - put your data in true Excel tables. That is the best method to use to extract data from Excel. It still isn't foolproof, but is far superior to getting data off of a plain Excel sheet.

Any filtering you do would work, but may slow down the import. If you have 1,000,000 rows of data that it is bringing in but you only want 10,000, You will have to let PQ bring in all 1M and discard the 990,000 you don't want. If it is 10,000 and you only need 9,900, that isn't a huge deal, but a million is. Try formatting as a table if possible and only bring in the 10K you need to start with.