Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
IkeWelsh
New Member

Data Load Erroring out Too Many columns

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, 
IKEDec-2023-Spreadsheet-Screenshot.jpgPowerBI_Data-Load_Error.jpgDec-2023-Spreadsheet-Screenshot.jpg

 

PowerBI_Data-Load_Error.jpg

 

2 ACCEPTED SOLUTIONS
Idrissshatila
Super User
Super User

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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




View solution in original post

v-huijiey-msft
Community Support
Community Support

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.

vhuijieymsft_0-1708489533834.png

vhuijieymsft_1-1708489533834.png

 

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!

View solution in original post

7 REPLIES 7
v-huijiey-msft
Community Support
Community Support

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.

vhuijieymsft_0-1708489533834.png

vhuijieymsft_1-1708489533834.png

 

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
Super User
Super User

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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




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!!

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

Idrissshatila_0-1708024863961.png

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




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.

IkeWelsh_0-1708026941944.png

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

IkeWelsh_1-1708027095547.png

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

IkeWelsh_2-1708027236395.png

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


Regards,
IKE

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.

 

JP_Wisco_0-1708034764158.png

 

@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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos
Follow me on LinkedIn linkedIn
Vote for my Community Mobile App Idea

Proud to be a Super User!




Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.