Total number of columns exceeded (but data was not changed)

[rdops]

Hi all,

I got stuck with an issue and hoping you can help me understand.

I am very new to Power BI and have no programming background, which makes things a bit more complicated.

My issue: I have a leave report that have been working fine for months. Yesterday when I tried to update it, I got an error related to the limit of columns (...Failed to save modifications to the server. Error returned: 'The total number of columns in the model is 16414, which exceeds the limit of 16000).

Point is: I did not change anything in the data source. Everything is exactly the same, same number of columns and rows so I have no idea why I am getting this. 

Then I decided to rename the sheets where the data is and to my surprise, it worked fine. Now I will have to redo the entire report, which is fine this time as is not too complicated, but I hate the fact that a) I dont know why my data didt upload until I changed the sheet name; b) I didnt know how to fix the issue; c) I had to redo the entire report.

If anyone could help me understand what happened and what I can do to prevent it from happening again that would be very much appreciated. Remember I am a novice with zero programming understanding. Note: I am working from home and the data is stored in Sharepoint. 

Thanks in advance,

Rose

[pjg]

Had this error when refreshing in Power BI Desktop, but not when refreshed when refreshing in the workspace.
Found in my case, data was pasted in error on the far right of the worksheet by someone and Power BI Desktop then pulled in all the columns on the worksheet exceeding the 16000 column limit.
All I did to resolve this issue was highlight all columns after my data and delete them.
This left only my 20 odd columns populated and fixed the issue.
Hope this helps someone else.

[krisby]

I suspect you didn't understand the problem Amitchandak, maybe cos you've not experienced it.
I'm now getting this same error on an XLS with 12 tabs, all names Apr 2023 to Apr 2024, with only 29 columns wide and 60 rows, no where near the 16000 limit, not even 1%, yet PowerBi throws up this error. 

[Anonymous]

Was this issue ever been resolved? I am encoutering the same issue. But I am importing CSV file, not Excel file. So there should be no issue on conflciting Table name and Sheet name. 

I was able to connect to 25 csv files without any issue. I have another 20 more, and I got this issue. I am not even create any visualization yet. Just create the data table has caused this issue. 

[amitchandak]

@rdops , It says you have more than 16000 columns, That is a big number. Please check and remove the unnecessary column.

Refer if this can help in clean up

https://radacad.com/power-bi-cleanup-tool-time-saving-with-power-bi-helper

[rdops]

HI Amitchandak, thanks for the reply. But that's my point. I have not changed the data set and the number of columns are the same as they always have been. Nothing changed. Then when I changed the name of the sheet it loaded correctly. So I haven o idea why it gave me this error in the first place and how (or why) it loaded when I renamed the sheet. I defintively dont have these number of columns. I only have data from A-T. And again, nothing changed since I last used the report a month ago. The data is updated but the number of columns or rows are the same.

[V-lianl-msft · Accepted solution]

Hi @rdops ,

Conflicting names for sheets and tables
If you have the same name as the worksheet, power Bi renames your table name when you connect to the excel file. It Can cause this issue.

For similar threads, you can also refer to:https://community.powerbi.com/t5/Desktop/Error-Msg-Column-Exceeds-16000-limit-but-there-are-only-100... 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.