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

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 😉😊

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 12 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, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.