Sometimes you need to change the data source in Power BI from a static data source such as a CSV file to a SQL connection
Here is an efficient way to do it to save you time and headaches
This guide shows you a quick and easy way to make the change without having to edit any of your existing visuals
This can be especially useful when moving from design or prototype to testing or production
But there is no obvious way to change the data source, so here is a really useful workaround
But first, make a copy of your pbix file…just in case
First add the new source
(It is important that all of the fields used in your CSV based prototype have the same field names otherwise you will encounter errors which you will need to resolve)
Click on Get Data
Select SQL Server and enter the details for the database you want to connect to
Then select the table or view and click load
You now have two data sources
The second one is the SQL connection
We will grab the details of this new connection
Right click and select edit query, then go to advanced editor
In the advanced editor
Copy the code from this entire block
Then close the advanced editor
Still in the power Query Editor
Select the original dataset, and go into its advanced editor
In the Advanced Editor
Highlight all of the code
And paste the new code from the clipboard
Click ‘Done’, then Close and Apply
Now delete the second dataset
The original CSV dataset is now replaced with the SQL connected dataset
Any calculated fields will still work and all visuals which referred to the original dataset will still work
This Power BI timesaver makes it easy to change from a static CSV data to a refreshable SQL one, without having to change any of your existing settings on any of the visuals already created
If the field names are the same, then it will all work
You now know how to change the data source in Power BI, the efficient way saving you time and effort
Subscribe to our channel to see more Excel Timesavers
Select Distinct YouTube Channel
Our Business Analytics Timesavers are selected from our day to day analytics consultancy work. They are the everyday things we see that really help analysts, SQL developers, BI Developers and many more people.
Our blog has something for everyone, from tips for improving your SQL skills to posts about BI tools and techniques. We hope that you find these helpful!
