Forum Discussion
Reducing data (columns) imported form Excel
My dataset includes several Excel files that are automatically generated from an online tool. I don't have control over what this tool exports, and as such, there are maybe a half dozen to a dozen unneccessary columns of data. I would like to find a way for these columns to be excluded from ever loading into Power BI, to try and make my data refreshes as fast as possible. I know I can remove them after they've been loaded into my data model, but is it possible to prevent them from ever having to load in the first place?
If it's relevant, these files are currently stored in SharePoint, which allows me to set up auto-refreshes in the Power BI Service.
Thank you!
Hi Anonymous ,
If the column name of the file does not change, the operation you thought before is right. What you do in the query editor is the first step in visualizing the data. When the data is refreshed in the future, as long as the column name does not change, the loaded data will not load the previously deleted columns.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hi Anonymous ,
If the column name of the file does not change, the operation you thought before is right. What you do in the query editor is the first step in visualizing the data. When the data is refreshed in the future, as long as the column name does not change, the loaded data will not load the previously deleted columns.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thanks V-lianl-msft !
Does that mean that is I were to insert a 'Delete Other Columns' step as my fifth step in my sequence as shown above, this would lead to the deleted columns never being loaded in the first place when I upload to Power BI Service?
Hi Anonymous ,
Please try to do this. My test was successful a long time ago.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous I don't think you can select columns prior to loading until there is a table in the Excel sheet with required columns if it was csv you could able to ignore columns but not sure if you can do it in excel.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
Thanks for replying so fast parry2k !
I wish I could go through my Excel sheet and actually delete the columns I don't want, right at the source, but since I'm downloading these reports repeatedly I'd rather just deal with Power BI being a little slow than having to delete multiple columns from multiple reports repeatedly.
I've included a screenshot of my first four query editor steps. The first three steps all deal with navigating to my file, and the four step is when my data finally appears in my query editor window. This is kinda just a rehash of my initial question, but is there a way to filter out some columns before I load my data? Or is my data actually loaded previous to the fourth step I'm mentioning? It would seem like the "Imported Excel" step would be where the data comes in, but I'm not familiar enough with the behind-the-scenes mechanisms to know.
The three steps prior to data loading; plus the data loading step
Anonymous depends how you are doing it, if you connect directly to excel sheet, navigation step will show the data and I guess you are combining multiple excel file, also I'm not sure if removing the columns in excel before loading will actually make a big difference, maybe you want to try to test it on sample files, remove columns and see if loads faster.
