Page 1 of 2

Edit Table Properties Problem

Posted: Sun Nov 13, 2016 4:42 pm
by dlamarche
Hello all. My first post here.
I am trying to follow the book Learn to Write DAX in 2013 and in parallel in 2016 so I can get familiar in both.
In 2013 When I click Design > Table Properties I see the columns and I can filter, sort and hide columns as shown on page 15, 16. There is also a dropdown Switch to: allowing me to change from Table Preview to Query Editor.

In 2016 I did the same exercise importing the same 4 tables from the same AdventureWorks_Learn_To_Write_DAX database. However clicking Table Properties from the Design tab opens the Edit Table Properties with the Switch to: dropdown disabled (Greyed Out) so I cannot switch to the Table Preview.

Trust me I replicated exactly the same steps for both versions of Excel (13 and 16) and in XL 2016 the Table Properties only show the SQL view: SELECT [Calendar].* FROM [Calendar]

Any ideas why my Excel Professional 2016 Plus does that. Unless in 2016 it is different! I did look everywhere for 20 minutes before I wrote this post.

Thanks a bunch,


Re: Edit Table Properties Problem

Posted: Fri Nov 18, 2016 7:36 am
by MattAllington
Hi Daniel

Sorry i missed this post earlier in the week (I was travelling). I have Excel 2016 and my "Switch to" box is not greyed out - you can select either. However once you make a change in this box with the Query View, you can't go back. So it sounds to me like this has happened to you. If you write a SQL statement to start with, you can't go back to the table view. If you start with the table view and make any changes in the query view, you can't go back. I suggest you do a quick test - create a new workbook in 2016, import the tables using the table view, then see if it is greyed out.

Excelerator BI - everything you’ll ever need to know about PowerPivot Training.

Re: Edit Table Properties Problem

Posted: Fri Nov 18, 2016 8:46 am
by dlamarche
Hello Matt,

Thanks for your reply. Whatever I try, the Switch to dropdown list is dimmed. I tried with another of my access database (a .mdb file) and another one (a .accdb) to no avail.
I'm puzzled because your book makes it simple to do but I can't. I'm a newbie in that field and I feel a bit shaken regarding this issue. Don't even know if it is really important. Funny thing is that it works well in 2013 (Same file same database). Just tried it in 2013 and I can see the Table properties just like in your book. In 2016 however (exactly the same database & table) the Edit Table Properties Appears but the Switch to dropdown list is disabled.
Last thing I did is open the Property window and clicked Validate, Save and Design (then OK) .... same thing.

Not too sure what you mean by Table view. Is it when I look at the table grid with the data in the PowerPivot window? If yes then that's what I've been doing.

I just wish I understood what's going on. I'm pretty literate with computers since I've been teaching Excel and Access since 1995 and done dozen of pretty cool DB that are still in use after 10 years). So you'd think I could come up with an answer. Google didn't come up with anything regarding that.



Re: Edit Table Properties Problem

Posted: Thu Dec 08, 2016 12:02 am
by gillyd123
Hi Daniel

Did you get anywhere with this? I have exactly the same problem on Excel 2016 and after 4 weeks of trying to find a resolution, nobody seems to know - not even Microsoft support??

Please share if you can throw some light.

Many thanks


Re: Edit Table Properties Problem

Posted: Thu Dec 08, 2016 7:58 am
by dlamarche
No Gilly,
Actually I'm getting a bit discouraged about all this. Seems that the book was written for 2013 and that's all.
We'll have to wait and see if a patch will solve this.
I new to all this and learning from the guy's book. I what happens doesn't match the book the it's hard to move on.

Wish I could have been of help.

Re: Edit Table Properties Problem

Posted: Sun Dec 11, 2016 7:13 am
by MattAllington
Hi guys. This issue has nothing to do with my book or the differences between Excel 2013 and 2016. I teach Power Pivot to hundreds of users using the same techniques in my book using All 3 versions of Excel.

This is a bug - I am trying to help resolve it but it is difficult.

Re: Edit Table Properties Problem

Posted: Sun Dec 11, 2016 2:57 pm
by dlamarche
Okay Matt,

No probs. Will let you tackle it at your own pace. Thanks for letting us know.


Re: Edit Table Properties Problem

Posted: Fri Mar 31, 2017 4:37 am
by Mhodges720
I am having this same issue, but I think I may have found a way to "deselect" a column and wanted Matt's take on it. If you select the entire column and right click on the heading a menu pops up. One of the options is "Hide from Client Tools". Will this do basically the same thing as deselecting the field? Thanks! Mark

Re: Edit Table Properties Problem

Posted: Fri Mar 31, 2017 6:08 am
by MattAllington
Hide from client too,s is very different to deselecting the column from the load event. The difference is the data is still loaded when you hide it. If your data model is small and fast then this won't cause any issues. But best practice is to "not load" any column you don't need. This improves (releases) space usage and improves performance.

Re: Edit Table Properties Problem

Posted: Mon May 15, 2017 6:53 pm
by csmith269
Just encountered this while working through the book. I was not able to change to Table Preview from the Query Editor. I ended up writing the Select statement instead to exclude the Fiscal columns. Do you know if this is a bug that is expecting a fix to be applied soon or is it my version of Excel? I am using 2016. Thanks,