Switch Between Power BI Data Sources Like A Ninja
Power BI Supports Many Different Data Sources...But How Can We Establish a System That Can Switch Between Them Quickly?
Often while working in Power BI Desktop, developers will be required to swap data sources depending on their environment. Perhaps you are working on a report querying data from a SQL Server connection requiring permissions. If you take that report off premise you will be unable to develop your report without replacing your SQL Server connection with some other source. This is often cumbersome and time consuming, especially when you need to re-create your SQL connection next time you are on-premise.
Luckily, there is a solution to this problem that has really amped up our production over this back and forth process. We employ Power Query to maintain and utilize multiple sources. When implemented correctly you will keep all of your applied steps, calculated columns, and measures present in your existing table in Power BI.
Lets go ahead and show an example of this....
Check Out This Example Of Switching Data Sources:
Here is a sample report we build using Sage 300 sample data. Our current Sales Orders table is coming from SQL Server, and had additional measures. Our goal is going to connect our Power BI Project to Excel data and have the report be build to conveniently switch between our two sources; SQL and Excel.
First step is to navigate to the Edit Queries window. Observe our current data, it has some applied steps, but otherwise it is just as we would expect.
The next step is to import a new data source. It can be from any source, but in my example, I am importing new data from Excel. At this point, it is very important that your headers in your new data source match those of the original source from SQL before any applied steps! This is the key piece that allows all of this to work. You can change your header names now if you need to.
This is a great start, we could just re-do our applied steps and use this new source in our report. The issue with this, however, is that this new table would now contain any of our measures or any calculations that refer to it, would be broken. We have to find a different way to get this data into our report. So for now we will un-check Enable Load for this data.
4) Advanced Editor
So here is where the fun begins. With our original 'Sales Order' table selected, go to the advanced editor, from the home tab. Here you are presented with Power BI's power query window which uses 'M' to query a data source. We are going to edit this to utilize our new source. Notice the structure of the query; it is let followed by a list of our applied steps, each step referring to a step prior. This is how power query manages order and instructions.
5) Create an Alternate Source
Next, we are going to insert a line in our power query. Have a look at the third line, we inserted this here with the following syntax
Source_(name) = #"(New Table Name)",
This is going to give our query two sources. One (the original) is named "Source" the other is "Source_(name).
Which one does our power query use?..
6) Here Comes The Quick Swap
Remember how we mentioned each step refers to a step prior? That is how we are going to tell our power query which source to use. Have a look at the high lighted text in the screen shot above; this is the first applied step that uses the Source, if we switch this to our new source, then this query will be forgetting about our original source and using only data from the first source. Click done, and notice your excel data is now flowing through our original table.
7) Click Close and Apply
Once you close and apply, you will see your new source reflected in your report. Notice how all our measures are still available and functional.
7) Enjoy Your Hot Swap Set-up!
Your all set-up! Next time you need to switch back to your SQL Server source, simply open up your advanced editor and swap sources in your first step. Done and done. Next time you need to hide sensitive data for a presentation you can set this hot swap up and save your sample data for your next one.
Thanks for checking this out and I hope that this tool will come in handy for you someday. Be sure to check out some of out other blogs here at Fresh BI.
Author, chris bradford
Power BI Coach
Honors in Mathematics/minor in Computer Science at University of Victoria.
Extensive problem solving and abstraction. Software development, report design.
Team FreshBI is an innovation machine and the blogs that Chris writes mostly emanate from the innovations that FreshBI does for its customers
Our Latest Blogs
Based in USA and Canada, we have helped hundreds of businesses achieve excellence & success through business intelligence.
Power BI runs in our blood, and we are ready to take your business to next level.