Forum Discussion

Tuna357's avatar
Tuna357
Frequent Visitor
1 year ago
Solved

Power BI pulling in all Excel columns

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 co...
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.