Visual Studio 2017 and the SQL Server Data Tool (SSDT), a tool that comes with Visual Studio, finally integrates the now familiar Query Editor (Power Query) capability to work with the Analysis Services Tabular Model. Query Editor is a data ingestion and transformation tool used by Excel Power Pivot as well as Power BI. Now, there are some differences when working on Query Editor within Visual Studio and Power BI / Power Pivot, however, and I’d like to discuss the biggest difference I saw:
All of your queries do not show up in the Query Editor! Let me explain what that means:
As an example, below image shows 5 tables in a model.
If you want to get back to Query Editor within Visual Studio, the only way that I have found so far is to
- Get to the Table Properties screen
- Click on the Design button, which will launch the Query Editor screen
The Query Editor screen seems peculiar, however. If you are used to Power BI or Power Pivot, you would expect all your tables to show up, but you will only see the single table that you requested the Table Properties for. See that big empty space. Where did all my tables go?
After posting this “issue” on Microsoft’s Yammer site, we got an answer basically saying – it is what it is, and I should use Expressions. For those of you not familiar with what an Expression is, a (Shared) Expression is encapsulation of “complex or frequently used logic through parameters, functions, or queries “.  The expressions show up in the folder structure of your Analysis Services model.
The natural question is, then, how do you create an Expression? An option to “Create an Expression” does not exist so you start out by creating a table.
- Import a new table.
- From the Query Editor window, ensure that a new table does not get created with the newly imported table (I know. I know.) by unchecking the Create New Table option.
- Close & Apply out of Query Editor
Now, you will see a new “shared” Expression ready to be used.
If you were to open the Query Editor screen for another table in the Tables folder, you will not see any other tables, but you will see the new (shared) Expression. With this, you can merge, append, or do whatever else you’d like.
With all this in mind, I have started a new way of creating the tables. I am NOT saying, this is the right way, but this has reduced the pain of not being able to see all the tables.
- I create ALL tables as Expressions first.
- I create the tables, the ones that will show up in the Tables folder, by creating a query with the source that references the expression as shown below
I hope this little trick helps as it has definitely helped me. Have fun BI-ing!