Change the Data Source TYPE of the Power BI Dynamically using Power Query Parameters

I have written a blog article and explained how Power Query parameters can be used to change the data source in Power BI. Another useful scenario of it is actually to change even the data source itself. For example, you may have two data sources with exactly the same data structure (columns), but different data. One of them is Excel, and another is the SQL Server database table. In this article, I show you how you can switch between these without needing to change your code and publish again. All of that using Power Query parameters.

Video

Explaining the scenario

Suppose you have the same data table with the same structure but different data in multiple data sources; one of them is a sheet in an Excel file, and another is a database table in a SQL Server database. If you want to change one source to another, you have to go to Power Query Editor and create a new table from the new data source. Your table’s connection to other tables will get impacted by this, you probably need to re-do the relationships. and If you used it in a visualization, you have to re-do all of those again. This type of change is time-consuming to apply.

However, there is a solution for this. You can use Power Query parameters to change the source dynamically. This change will require a bit of preparation, but then after that, the change would be as simple as changing a parameter value. You won’t need to re-do the relationships, or visualizations, or even re-publish. Let’s see how this works. I recommend reading the two articles below as they are highly related to this subject;

• Power Query parameter Vs. What-If parameters
• Using Power Query parameters to change the data source

The sample scenario above is about changing the data source type from Excel to SQL Server database. However, the same method can be used for any data source. You can change from a Snowflake data source to a web API for example. The concept and the method explained in the below scenario, works for any data sources.

Sample Power BI report

In my sample Power BI report, I have a data table coming from an Excel spreadsheet; AdventureWorksDW202.xlsx.

The table FactInternetSales is what I get from this data source;

I have a simple report showing the count of rows in that table, and the sum of the SalesAmount column as below;

Now, we want to be able to change from this data source to a SQL Server database table source with exactly the same structure, but different data.

Now that the problem is explained, let’s see how the solution is implemented.

Create a new query from the second data source type

The first step is to create a query from the second data source type (in our example; SQL Server database). This helps us to get the part of the code that fetches data from that data source.

Set the data source settings;

Once, in the database, select the table and continue to get the table in the Power Query Editor (Using Transform Data in the navigator window)

Once the new query is available in the Power Query Editor, check and see if the structure of columns (meaning the column headers, the order of columns, and their data types) are similar to the previous query you had from the Excel source. In the screenshot below; there are two FactInternetSales queries. FactInternetSales is the first query coming from the Excel source, and FactInternetSales (2) is the new query coming from the SQL Server database table.

Extract the M code for the new data source

After confirming that the data structures are similar, go to the Advanced Editor from the View tab, or the Home tab;

This will give you the M script of the query sourced from the SQL Server database table;

As highlighted in the screenshot above, select the part inside the LET section of the script, and copy it using Ctrl+C

Inject the code in the previous query

The next step is to bring the code above inside the previous query (the query sourced from the Excel source). For this purpose, go to the Advanced Editor for the first query;

In the Advanced query editor, add one comma at the end of the last line before IN, and paste the code copied from the previous query. Do NOT click on DONE just yet!

This code is not ready to work yet. As you can see, there are errors in the code. That is because we have variables with the same name in the code. For example; the Source variable name is used twice, once for the previous query and once for the new query.

Change any variable name that is repeated through the code. For example, I changed the Source to SourceSQL for the later part of the code that gets data from the SQL Server data source. I also changed that anywhere else in the code after that line that the Source had been used. This process has to be done carefully for every variable with the repeated name. You should follow this part until you get the message at the bottom saying No Syntax Issue detected. I highly recommend reading my article about the basics of M to understand more about this scripting language.

Click on Done. your code will still fetch data from the Excel source in this example because there is still one more step to do.

Power Query Parameter for the data source type

Now that we have the bits and pieces ready for parameterization, go and create a new Power Query parameter in the Power Query Editor;

Set the new parameter of type Text and the default value as Excel. You can call the parameter as the DataSource.

Now let’s use this parameter’s value in the code.

Conditional switch of the data source based on the parameter’s value

Go to the Advanced Editor of the first query one more time;

Add a comma on the last line before M, and add the script like below;

The concept is to write a conditional expression like the below;

result = if (DataSource="Excel") then <variable with data table from Excel> else <variable with data table from SQL Server database>

Not that the piece of code above isn’t an actual M code, it is a psuedue code explaining what that code is doing. the result variable here then is used in the IN section. This makes sure that whatever variable the data is coming from it will then be shown in the result set;

Click on Done, and then you can remove the query from the SQL Server database table which is not needed anymore (FactInternetSales (2)), we already got the code and injected it into the other query.

Testing the solution

Your solution is ready. Now you can test it by Close and Apply and going to the Power BI Desktop window;

If you want to change the data source, go to Edit Parameters;

Change the parameter value to SQL (In this example, you can change it to anything except Excel, because that part goes to the ELSE condition of the IF statement. Note that Power Query is case-sensitive)

Click on Apply changes;

And after the refresh, the data from the new data source will be in the visualization;

Congratulations! you have a report with dynamic data source type now. This parameter can be also changed from the Power BI service under the dataset settings;

You will need to refresh the dataset after the change of the parameter value.

There are, however, some very important considerations you need to know about this solution;

Important considerations

The method above works beautifully, however, there are things you need to be aware of this solution;

1. The change of the data source type solution mentioned here is not for end-users. Because the user has to have access to the dataset settings and able to refresh it (if in Power BI service).
2. This method requires a refresh of the dataset afterward. This can be done by Apply Changes or Refresh in the Power BI Desktop, and Refresh in the Power BI service.
3. Power Query is case-sensitive. The values “Excel” and “excel” are two different values. I stronlgy suggest creating a list of values as a parameter instead of a text parameter which can be written in any methods. I’ll write a blog separately about it.
4. This method works for ANY data sources. You can change from a snowflake data source to Oracle data source for example.
5. If any changes you do in the main query AFTER this step, there won’t be a problem. because they will be done after the result step.
6. You may need to have another table and a visual related to that in the report showing what data source now you are switched to. So that the user doesn’t get confused.
7. if your data sources are on-premises data source, you need to define gateway data sources for each of them.
8. If you want to switch between two data sources of the same type, there is an easier method as I explained in my article here:

I hope you use this in your solutions and create better Power BI reports using it. Let me know if you have any questions in the comments below.

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.