cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jperry1120 Frequent Visitor
Frequent 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
Super User
Super User

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

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?


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


andre
Advisor

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

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

jperry1120 Frequent Visitor
Frequent Visitor

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

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

Super User
Super User

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

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


hugoberry Member
Member

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

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

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

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

alforc1 Frequent Visitor
Frequent Visitor

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

@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

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

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.

alforc1 Frequent Visitor
Frequent Visitor

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

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.

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 277 members 2,965 guests
Please welcome our newest community members: