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 “. [1] 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!
business intelligist 
Being familiar with PowerBI, I have been wondering the same thing ever since I started looking into Analysis Services. And trying to get back to the query editor that shows all the queries has been quite a pain! I do hope they fix it in the future.
Thanks man, this has saved me some pain. I am migrating power bi models to tabular and your artical showed my why i could not use the query editor under tables to make modifications. Validating just kept spinning.
This was very, very useful for me.
Thank you very much.
This was very, very useful for me. A lot of time spared.
Thank you very much.
Thanks for this. I’ve successfully used it to allow me to mostly promote Power BI models into analysis services (via lots of copy paste). Is this still your preferred method for creating tables or has Microsoft slipped something new in yet?
There is no easy way to promote Power BI models to Analysis Services… There was a feature in Azure to restore Power BI to Azure AS but it’s been depricated… There is a great tool – Tabular Editor, that has many cool features.. Maybe there is a way to back up from Power BI and restore to AS but I have not done it yet myself… Tabular editor does allow to bulk cut and paste from one model to another so it might be worth a try
Hi,
First of all, great stuff. I followed the steps, it worked but when I want to process the table. It displays the follow error Failed to save modifications to the server. Error returned: ‘An M partition uses a data function which results in access to a data source different from those defined in the model. How can I solve this problem.
Thanks in advance
I have been breaking my head for this…thanks a lot!!!
Thanks. I had just got my head around using power query with Power BI and now my company wants to use it with AAS with Visual Studio. I thought I was going mad not being able to get back to the Query Editor from Visual Studio! I haven’t been able to find anything in the various tutorials, so was VERY grateful and reassured when I came across your article.
Use the Expressions in Tabular Model Explorer
ohhhh
I’ve been avoiding the “Power Query” module and using legacy import and did sql views prior to import because of this.
This seems to work well.