How to filter excel data during load to Power BI

[SB1]

Hi All,

I am working on a heavy excel data set which is like 1600MB & will continue to increase every month. I don't need all the columns (80 plus) for my analysis. Is there way I can filter/specify which columns should be loaded to BI?

I have seen many post regarding using sql query to filter data, however cannot find a solution for excel dataset. 

Since the data is huge I cannot open excel spreadsheet & filter there as well.

Any suggestions?

Thanks for your help!

Regards,

SB

[Tahreem24 · Accepted solution]

You just need to exclude only one time while loading into power bi and every time it exclude that columns and saves your loading time. Please follow the below steps:

Open PBI Desktop - -> Get data - -> select your excel file - -> and click on transform and edit instead of load button - -> then remove unwanted columns and records - ->save and apply.

Please don't forget to give Kudos and accept this as a solution if it helps you.

[v-lid-msft]

Hi @SB1 ,

How about the result after you follow the suggestions mentioned in @itsme 's post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[itsme · Accepted solution]

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.

[SB1]

Thank you for the solution - It works! Much appreciated 🙂

[Tahreem24 · Accepted solution]

You just need to exclude only one time while loading into power bi and every time it exclude that columns and saves your loading time. Please follow the below steps:

Open PBI Desktop - -> Get data - -> select your excel file - -> and click on transform and edit instead of load button - -> then remove unwanted columns and records - ->save and apply.

Please don't forget to give Kudos and accept this as a solution if it helps you.

[SB1]

Thanks Tahreem- this works! 

Regards, SB

[Tahreem24]

@SB1 You're welcome. I will really appreciate yout THUMBS UP/KUDOS for support.

Good Luck!