Change the T-SQL Script of a Power BI Table without Add/Remove from the Model

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

If you have been using T-SQL queries to build the tables of your Power BI data model, you probably know what I’m talking about. Sometimes after a while using a query in the Power BI model, you might change the script and want the change to be applied in the data model. One of the ways that are common to use (which is not the right way) is to remove the table and add it using the script again. This would cause losing all the calculated columns, DAX measures, relationships, possible hierarchies, and anything for the modelling of that table. Instead of doing it that way, you can easily change the script of the existing table. In this article, I’m going to show you how to do so. If you like to learn more about Power BI, read Power BI book from Rookie to Rock Star.

Using T-SQL Scripts As a Source in Power BI

If you are connecting to SQL Server database, or any other relational database systems (Such as Oracle, MySQL, DB2, etc), you can use the T-SQL script as the source of a Power BI report. All you need to do is to Get Data, choose the right data source type (in this example, it is SQL Server);

After entering the Server and Database, you can click on Advanced Options, and then enter the SQL statement there:

There is also a Data Connectivity mode, which you can choose to be DirectQuery or Import depends on the architecture you are using for Power BI.

The Challenge: Remove/Add

Now, let’s look at what is the challenge if you want to make a change. Let’s say that table created with the script above, has been used in a data model like below, with a bunch of relationships

And also it has some DAX calculations;

Now, if you decide to make a change in the SQL statement (let’s say removing a column, or adding one, or changing somewhere in the script). and then you end up with a new script like below;

Some of you might say that, why not using Power Query for changes in the data? while I’m most of the time on the Power Query side for making changes, in this article we are focusing on those of you who are more comfortable writing T-SQL statements than doing things in Power Query, So let’s see what would be their point of view in Power BI Desktop.

There is no place in the Power BI Desktop to change the SQL script of the existing table, so you might go ahead with removing the table, and adding it again. this means losing all the relationships to other tables, DAX calculations, hierarchies and any other modelling configurations. And then after adding it again, you probably need to re-do all those changes which will easily take hours or days of your time.

I have good news for you, there is a better way to do that, let’s see.

Change Query

Power Query is the data transformation component of Power BI. Anything that comes into the Power BI data model comes through Power Query. In Power Query transformations, there is a step for reading data from the source, and other steps for applying transformations on it. All you need to do is to find where is the step that reads data from your SQL statement. And then you can change it there. Here is an easy way to do that:

Go to Query Editor from the Home tab in Power BI Desktop;

In the Power Query Editor (which is a different window from the Power BI Desktop), click on the table name you want to change the SQL statement of it.

Then in the right-hand side, under Applied Steps, find the Source step (the very first step on that list), and click on the setting icon besides it.

This would be the configuration of the very first step that you read the data from that data source, which in this case, would be the place to enter the SQL statement. Paste your changed SQL statement here, and then click on OK.

Now you would see the change is applied in the Query Editor. As the last step, go to Home tab in Power Query Editor, and click on Close & Apply.

That’s it. You have changed your T-SQL statement and the table without adding/removing it and doing a lot of re-do. All of that with the help of Power Query. All your relationships, calculations, and modelling configurations are still there intact!

I always in my courses explain to students that Power Query is the dark side of the force. And the dark side of the force is the pathway to many possibilities that you might not know, I can teach you that 😉😊

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

4 thoughts on “Change the T-SQL Script of a Power BI Table without Add/Remove from the Model

  • Nice. Would be cool to show how to get the TSQL from an existing Powey query to apply the same technique….for those of us not knowledgable on TSql.
    This is useful for those of us taking over reports or optimising existing reports that have had extensive transformations done that can’t be query folded……Grouping, unpivot etc.

    • Thanks
      For that, you can right-click on the latest step in the query, and click on “Native Query” that would be the T-SQL query generated by Power Query engine
      Cheers
      Reza

  • I would assume we can make our query dynamic by using Advance Editor, with parametric values which a user can change at run time in Power BI for the Query to get updated.
    i.e. set parameters to point to a server and database dynamically from within Power BI.

    • Yes, you can make anything parametric. the server name, the database name, the query, and almost anything.
      User can change the values, however, then they need to click on APPLY CHANGES to get the reflection of the change, which is not the best user experience. Parameters are usually used for automating the ETL process like what I explained here.
      Cheers
      Reza

Leave a Reply

Your email address will not be published. Required fields are marked *