Forum Discussion
Helper IHow to access directly the sheet from excel file?
Hi,
In one single query i need to download the same sheet from multiple excel files that are 50 MB each.
The selected sheet is quite small and i think is less than 1MB.
To load the sheet is used the standard function:
Excel.Workbook([Content], null, true){[Item="Sheet1", Kind="Sheet"]}[Data],(Content is the Binary of the excel)
I noticed that when i refresh the data, power bi download every excel and not just the single sheet (50MB vs 1MB)
How can i access directly to the sheet without having to load the entire excel file?
Hello stenford23
I can tell you that power query is only reading the data it needs. So in your case it's reading only this single sheet. What it could cause this is the used range in Excel. So maybe you filled only little data, but the used range is really big. So go to this sheet and press CTRL + end. Probably you will see that the used range is way bigger than your really data. Delete al not needed cells and make a save as. Retry to load the data to power query
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy
6 Replies
Jimmy801Community Champion
Hello stenford23
I can tell you that power query is only reading the data it needs. So in your case it's reading only this single sheet. What it could cause this is the used range in Excel. So maybe you filled only little data, but the used range is really big. So go to this sheet and press CTRL + end. Probably you will see that the used range is way bigger than your really data. Delete al not needed cells and make a save as. Retry to load the data to power query
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too
Have fun
Jimmy- stenford23
The used range is correct in the original file and the sheet alone (copy paste on new excel file) is 1MB file size.
When is refreshing i can see that power bi is loading the entire file otherwise it should load less than 50MB.
- Jimmy801
Hello stenford23
however I tried to reproduce it now. I created a file with to sheets. One contains a table with on cell and the other 3,5 million cells. When i refer to the sheet with one cell it takes nothing to load, when I refer to the other, it loads 10 MByte. You could post all your m-code here to check if there is something else
Jimmy
- stenford23
I inspected every file and i discover there was one that has this issue. Thanks for the help.