How to access directly the sheet from excel file?

[stenford23]

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?

[Jimmy801 · Accepted solution]

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]

I inspected every file and i discover there was one that has this issue. Thanks for the help.

[stenford23]

@Jimmy801  

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]

@Jimmy801 

This is the code to load the multiple sheet from different file inside a folder:

//list every file under path folder
data_file_listed= Folder.Files(path),

//Extract every table
add_table = Table.AddColumn(
    data_file_listed, "table",
    each 
        Excel.Workbook([Content], true, true){[Item="Sheet 1", Kind="Sheet"]}[Data],
    type table
),

keep_table = Table.SelectColumns(add_table,{"table"}),

//expand table
expanded_table = Table.ExpandTableColumn(
   keep_table, "table",
   {... (Column name) ...}
),

[Jimmy801]

Hello @stenford23 

I can't reproduce your scenario even though I'm using your code. When I read from 2 files with a size of 10 Mbyte, but I'm only reading the sheets with on cell it loads in an instant. 

BR

Jimmy