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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jperry1120
Regular Visitor

What is the maximum number of columns Power BI Desktop allows?

What is the maximum number of columns Power BI Desktop allows before it stops showing records in the Data Model?

14 REPLIES 14
Anonymous
Not applicable

https://support.office.com/en-us/article/power-query-specifications-and-limits-5fb2807c-1b16-4257-aa...

 

This document shows the Column limit for Power Query is 16,384...in line with some of the other comments on this thread of "Power BI complaining about 16,000 columns"

Hi Chris,

Quote:

'... when I display in a Matrix it will only display around 104 columns it then stops showing any columns after that point...'

End of quote.
I think he asked for limitation in VISUAL, not the data table. And my answer related to this question.

 

Regards,

Adam

AdamBoltryk
Resolver I
Resolver I

Hi All, I can confirm to limit of column displayed in visual. In my case when I created table with week numbers in columns I've got table from WK01|16 till WK08|18. It's mean (simple math) 52+52+8 = 112 columns 🙂 

alforc1
Frequent Visitor

@jperry1120@Greg_DecklerHi, I am having the same issue with this. I am also losing my matrix total when running more than 104 columns of data.

Anonymous
Not applicable

you can load only 104 columns? that is a really low number... where are you loading this from? if it is from a multi-dimensional data source, your data base needs to convert to tabular base, because PBI only works with tabular model when importing data. in this case, your data base may not have enough resources to convert to tabular. if this is what is happening, try loading using directquery option instead of import.

Hi, Thank you for coming back to me.  I can load more than 104 columns.  They appear correctly when displaying in a chart format but when I display in a Matrix it will only display around 104 columns it then stops showing any columns after that point.  If I reduce the number of columns in the matrix it does work & gives a row total. When I increase above the 104 columns the matrix no longer displays a total and will remove some of the latest weeks. For Example if I want to show 2 years of data by week it is fine but If I go over 104 weeks it then stops showing th row totals and doesnt show the latest weeks that I know are present because it does display them in a chart format.  I am using a multi-dimensional data source.

Anonymous
Not applicable

hum, ok. You mean the Matrix visual object, at first I thought it was the data table itself. My bad. In this case, I think it is a limitation of the object, and I don´t know any solution on this case. Maybe you can try to get a new custom visual, but the appearance of the new visual will be slightly different. How many levels of drilldown do you have? Maybe if you lower the quantity of drilldowns it may improve the visual, idk.

Hi Thanks again for coming back to me. 

 

I have 4 levels of drilldown in place currently.  I will try both solutions that you have suggested.  Again thanks for the feedback.

 

 

hugoberry
Responsive Resident
Responsive Resident

I've seen that Power BI complaints if you try to load more than 16000 columns.

The actual number that I've managed to test is 15,986.

 

On a machine with sufficient memory you can run the following query to generate all of these columns

= #table(null,{{1..15986}})

Anonymous
Not applicable

is it something related to maximum cache data? under options, you can see 4096MB as default.

did you tried to set this value higher?

large.png

andre
Memorable Member
Memorable Member

@jperry1120  Total number of columns in a table depends on the total number of Measures and Calculated Columns associated to the same table.

 

The maximum number of 'Columns + Measures + Calculated Columns' for a table is 2^31-1 = 2,147,483,647 (SQL Server 2016)

Greg_Deckler
Super User
Super User

To my knowledge, there is no published limitation on the number of columns, are you running into an issue where records are no longer displaying?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler Yes, I have 377 columns and over 20,000 records that I can see in the Query editing mode, but when I try to view the data in the Data Model, there are no records. If I delete all but about 10 of the columns in Query edit, the records appear in the Data Model.

Hmm, that is a tad less than 2 billion. I wonder if there is some issue related to length of column names. Any chance you could try shortening some column names and see if you can get all 377 columns? Long shot, but might work... Probably more of an issue with the GUI rendering rather than an actual limitation, so the real limit may very well be 2 billion, but if the GUI can't display it, doesn't do you much good I suppose.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors