Power BI pulling in all Excel columns

[Tuna357]

Good Day,

I have a board that has begun pulling in all columns from Excel, causing refreshes to stop.  I'm not certain how/why this started, as it has run without issue for a long time.  I can copy the data from original sheet and paste on another sheet and that will sometimes take care of the problem temporarily, but it doesn't solve it.  

Would anyone have any advice on what may be going on here and how to fix?  Thanks in advance!

[RossEdwards]

Best advice I can give you is to use a "Remove other columns step" and a filter step that removes black rows.  Excel keeps a track of what columns and rows have had at least 1 cell get used.  This creates a Data Area that Power BI is using to become the bounds of what it draws from excel.

Because Excel is so often a hand edited file, people accidently do things that can extend that data area out to the limits without realising.  So using "Remove Other Columns" helps make your query more robust.

[v-tsaipranay]

Hi @Tuna357 ,

Thank you for reaching out to the Microsoft Fabric Community. Also thank you @KNP for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

The error you're seeing indicates that Power BI is pulling in far more columns than expected, likely due to connecting to an entire worksheet instead of a defined table.

This often happens if the Excel file contains hidden or blank columns that are still counted as data. To resolve this, open the Excel file, use Ctrl + End to check the actual data range, and remove any unnecessary rows or columns. Next, convert your data into an Excel Table (Ctrl + T) and direct Power BI to connect to this table instead of the whole worksheet.

In Power Query Editor, make sure the source is set to the table and use “Remove Other Columns” to keep only what you need. If problems continue, try saving the cleaned data in a new Excel file and reconnecting.

Using structured tables and removing extra data helps keep your model within Power BI’s limits. You can refer to the documentation for more details.

Import Excel workbooks into Power BI Desktop - Power BI | Microsoft Learn

Tutorial: Shape and combine data in Power BI Desktop - Power BI | Microsoft Learn

Hope this helps. Please reach out for further assistance.

If this post helps, then please consider to Accept as the solution to help the other members find it more quickly and a kudos would be appreciated.

Thank you.

[v-tsaipranay]

Hi @Tuna357 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-tsaipranay]

Hi @Tuna357 ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[KNP]

Hi @Tuna357,

Are you able to share the code you're using to extract data from the sheet? (change any sensitive info)

Alternately, the ChatGPT pasted answer from @DanielaOliveira below may have some useful tips.

[DanielaOliveira]

Good Day,

This issue is related to the data model exceeding the column limit in Power BI (or the tool you are using). The error message says:

"The total number of columns in the model is 17766, which exceeds the limit of 16000."

This usually happens when the data model unintentionally pulls every column from the Excel source, including hidden columns, empty columns, or extra system-generated columns.

---

Possible causes include:

• The Excel file might have hidden columns or extended blank columns Excel considers part of the dataset.

• The data connection could have shifted from a defined table or range to the entire sheet accidentally.

• There might be extra data beyond the intended range (ghost columns).

• A previous filter or step in Power Query may have been removed, allowing all columns to be loaded.

---

Solutions to try:

1. Check your Excel file:

• Open Excel.

• Press Ctrl + End to see where Excel thinks the data ends.

• If it goes far beyond your actual data, delete all empty rows and columns beyond your dataset. Save the file.

1. Convert your dataset to a Table in Excel:

• Select your data and press Ctrl + T to create an Excel Table.

• Connect Power BI to the Table instead of the full sheet.

1. Review Power Query steps:

• Open Power Query Editor.

• Check the Source step. Make sure it connects to the Table, not the Sheet.

• Apply a "Remove Other Columns" step to select only the needed columns.

1. Manually select columns when importing.

2. If needed, recreate the Excel file:

• Save it as a new file to clear possible corruption.

• Reconnect this cleaned file to Power BI.

---

Best Practices:

• Avoid connecting directly to entire sheets.

• Always use Excel Tables as the data source.

• Regularly check the Excel file for empty or unintended columns.