What is the maximum number of worksheets within an Excel workbook that can be imported into Power BI

[jel888]

Hello,

I have an Excel workbook (file size under 1 MB) with 137 sheets (power query data sets uploaded into the wkbk).  Only the first 100 were imported.  Why? I don't have a large file nor a large data model.   I missed seeing what the Sumary page said about the import (also how do you see that info again after the wkbk is on your dashboard)? I've looked online and can't find a valid reason why this is so.   I am using the free online Power BI version.

[GilbertQ]

Hi @jel888, I created an Excel Workbook with over 200 sheets and imported it successfully.

Attached is the image. As you can see there were 221 queries with each one being a seperate sheet.

I am using Power BI x64 (Sep 2016) and Office 2010 x86

I would suggest if possible to load your data using Power BI Desktop?

[gpsdnet]

Hi @GilbertQ,

How much memory did you have on your system to open 200 sheets ?

I have a system with 16 Gb Memory and my Excel is giving up on 100 sheets.

[GilbertQ]

Hi @gpsdnet

When I did the test it was with a small Excel sheet.

It would appear that it is not Power BI that is the issue, but due to the sheer size of the Excel file, that your PC is running out of memory which is the cause of the error.

I would suggesting possibly consolidating the sheets, or getting more RAM for your PC.

[gpsdnet]

Hi @guavaq,

How much memory did your computer have to be able to open 200 sheets. I have a system with 16 GB, and it is giving up on 100 sheets.

[v-ljerr-msft]

Hi @jel888,

Based on my research, I haven't been aware of any limitation on number of worksheets within an Excel workbook either.

Could you try re-uploading or re-importing the Excel file into Power BI Service to see if the issue remains? You will be asked to replace the Excel file, and your existing dashboards won't be affected.

Regards

[jel888]

Done, twice, same result. 😞  I cleaned up information in the file, deleted the previous one from Power BI and uploaded the new file.  Note that the data in each sheet orignal was imported via Power Query from Excel files on SharePoint. (If that helps in the analysis of why this occurred).

[v-ljerr-msft]

Hi @jel888,

Done, twice, same result. 😞  I cleaned up information in the file, deleted the previous one from Power BI and uploaded the new file.  Note that the data in each sheet orignal was imported via Power Query from Excel files on SharePoint. (If that helps in the analysis of why this occurred).

---

That's a little weird. Could you share a sample Excel file which can reproduce the issue in this case? You can upload it to OneDrive or Dropbox and post the link here, or just send the link to me in private message. Do mask sensitive data before uploading.

Regards

[dchager]

Could be that yor network permissions are interfering. Also, are you using 64-bit software and hardware?

[jel888]

Hello,

I'm on my Macbook Air, tethered to the net (not work network).

[Greg_Deckler]

I believe that you should be able to click on the gear icon next to your Source line in your query to see the original import interface again.

[jel888]

Ummm, perhaps it's because I'm new to Power BI, but where in the online version (and note I'm on a MAC so I don't have a Power BI Desktop version) do I see my query?  For now, all I see is my file indicated on the dashboard and the details of my data in the dataset for that file.