How to filter excel data during load to Power BI

When you open an Excel workbook in Power Query, usually the first step is called "Source", and PQ will add additional steps to open the workbook. Delete the steps after 'Source'. Now, locate the 'Data' column where you can click in each cell and preview the data.

Locate Data column and preview tables if needed

If you have more than one table, you can filter the tables of data in the Excel workbook that you want to keep or remove. This step may not be applicable if you only have one table (many times I have tables on multiple sheets within a workbook that I'd like to append, so those tables would show up in this step).

In following screenshots I forgot this next step, so disregard the columns 'Name', 'Item', and 'Kind' in those screenshots. Now right-click on the 'Data' column and select 'Delete Other Columns'.

Now, click the dropdown button in the 'Data' column header and select the columns you want to bring in - make sure 'Expand' is selected. The 'Expanded Data' step will be added to the query and you'll see your new table. If you find out there are additional columns you want to keep or delete, you can always open the formula bar in this step and add/delete the column names. You will see the columns brought in are in the first bracket. By default, the second bracket has the same column names, but you can change the column names in the second bracket. For a column to show up, it needs to be in both brackets by position separated by comma. You see there are 5 column names in the first bracket and five in the second bracket. Column1 and Column2 take up positions 1 and 2 in the first bracket and will have those positions in the second bracket, regardless of the name you choose.