Forum Discussion
Helper IHow to access directly the sheet from 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
Community ChampionHello 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
Helper I
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