Data Load Erroring out Too Many columns

[IkeWelsh]

Hi everybody, 
I'm trying to load some data into PowerBI from Excel, but every time it's "Creating Connection in Model" I get the following error for my Data files: "Failed to save modifications to the server. Error returned: 'The total number of columns in the model is 16457, which exceeds the limit of 16000. '

I don't understand why I would get this error, especially when my files only have about 8 columns and a maximum of 400 Rows. Which in no way comes close to 16,000 Columns.

I have inlcuded some screenshots showing the error and one of my data files for your anaysis and review.

Thannks for any and all assistance.

Regards, 
IKE

[Idrissshatila · Accepted solution]

Hello @IkeWelsh ,

what I may suggest that there are blank columns that are being read from the excel, check in power query how many columns you have, and if there are these blank columns then use the choose columns option to only choose the columns you want.

https://youtu.be/HIS_brYNpnI?si=DBFZjCcgEb11mBCI

[v-huijiey-msft · Accepted solution]

Hi @IkeWelsh ,

Hope everything is going well.

The error message reads: "The total number of columns in the model is 16457, which exceeds the limit of 16000."

Although your actual number of columns is only 8, it shows 999+ columns in Power Query.

You can remove blank columns in Power Query by following these steps:

Open Power Query, hold down Ctrl and select the 8 columns you need, select Remove Columns>>Remove Other Columns on the Home tab.

After deleting the blank columns, check other tables in the model to see if there are similar issues and if so, follow the same steps to delete the blank columns.

After cleaning the data, close the Power Query editor and apply the changes. Then, refresh the data model to ensure it reflects these adjustments.

Another possible cause of the error is a name conflict between the worksheet and the table.

If it has the same name as a worksheet, Power BI will rename the table name when connecting to the Excel file. It may cause this problem.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Idrissshatila · Accepted solution]

Hello @IkeWelsh ,

what I may suggest that there are blank columns that are being read from the excel, check in power query how many columns you have, and if there are these blank columns then use the choose columns option to only choose the columns you want.

https://youtu.be/HIS_brYNpnI?si=DBFZjCcgEb11mBCI

[IkeWelsh]

Hi Idrissshatila,

I don't have 1000 Columns,
I don't even have 10 Columns

I have 8 columns and maybe 400 (at most rows).

This makes no sense as to why it's saying there are so many columns!!

[Idrissshatila]

@IkeWelsh can you show us how many columns you have in power query, it will show in the down side.

[IkeWelsh]

Hi there Idrisshatila:
It shows 999+ columns, BUT those extra Columns are Blank and it should NOT be counting them as there's no data in them.

Plus the Table the data is coming from is only using the first 7 columns and nothing else

As you can see in this Screenshot the table is only using A1 - F190 Which is ONLY 7 Columns

Doesnt make sense why PowerBI is saying there's extra coumns when there's not
And the Power Query I'm using for the data is only using 4 columns

So that shouldn't be impacting the data implort/ load either.

Regards,
IKE

[JP_Wisco]

It is possible that one or more of your queries is trying to read the sheet as its source rather than trying to read the table. If you can step back through your Source and Navigation steps for each of your queries, you should be able to see which one you are actually trying to query. Duplicate your query before trying to switch your source or navigation as that will usually erase your subsequent steps.

I always try to use named ranges or tables when querying my current workbook. Entire worksheets are saved for other workbooks.

[Idrissshatila]

@IkeWelsh , power bi actually counts them and they're being read from the excel, so if you want copy your columns to another excel sheet and connect to the new excel.

it happens and you now know what's going on and how to solve it.