How to Change a Data Source for a table in Power BI?
Having worked on Power BI for some time now, I have observed that we come across situations where we need to change the data source e.g. from SQL Server to Excel or vice versa.
This could be time-consuming if we go in a traditional way which is to create two separate reports with different data sources. And other is an easy way or should I say a quick way to do it.
Let’s say we have a report which needs to be replicated for two different companies with the only difference being the data source. One is a SQL database and the other have Excel as the source, with the same table structure.
Now, to avoid the rework, we will create a copy of the report and in the duplicate report, we will change the data source from SQL to Excel.
How to do it?
We have SQL Server data source-
Are the field names the same? If so, what I’ve done to make this easier and it seemed to work, is:
- In Edit Queries, Import the data from Excel.
- Once you import the data some steps will be generated in Power Query under Applied Steps. To view these steps, click on the Excel imported table and click on Advance Editor.
- In the new source’s Advanced Editor pane, copy the commands with CTRL-C. Clicked Done.
- Then open the Advanced Editor for the SQL Table [1], replace the source with copied information from the previous step.
- When you apply the changes, this should point your old query to the new source, and you should be able to delete the new table you added as it will now appear twice.
Please NOTE: You must take care here if you performed any transformations. You may have to repeat them or edit the commands accordingly.
If the following error occurs while deleting the duplicate table :
Then there are some steps that should be applied to remove the error.
- It sounds like you have some sort of relationship that references the table. Go into your relationships area and remove any relationships connected to that table. If you still get an error.
- Check whether you have any DAX formulas that reference that table. It may be a Calculated Column or Measure. If any then remove it.
- Go to Edit Query, under the Advanced Editor section check all the applied steps. Check if any query is added as a reference or some blank filter in the applied steps, then remove it also.
After applying all these steps, you will be able to delete the duplicate table.
Blog disclaimer: This is a professional weblog, and we have invited experts to share their thoughts, expertise , perspectives and knowledge. The opinions expressed here are purely representing their personal views and not those of any institution, employer or company.
“Extend your learning with our specialized courses"
info@voksedigital.comSource: Microsoft Power BI
