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.
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 😉😊
6 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.
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
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.
Thank you for your Power BI knowledge transfer .
(1). How can I get this on Power Bi Service?
(2). How can get Power BI Metadata API ?
You can put the entire script in a Power Query Parameter. and then you can change the parameter value in the service, under dataset settings page.
for your second question: What do you mean exactly by “Power BI Metadata API”? what do you want to do?