Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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,
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.
Thank you for the solution - It works! Much appreciated 🙂
Thanks Tahreem- this works!
Regards, SB
@SB1 You're welcome. I will really appreciate yout THUMBS UP/KUDOS for support.
Good Luck!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |