Replace data sources in Power Query with published Power BI dataset
This post is also available in:
German
Occasionally, as a Power BI developer, you are faced with the task of replacing the data source in existing reports with a published Power BI dataset. Especially if there are already several reports that were created with a copy of a PBIX file and are now to be consolidated to the same dataset.
The question here is how best to proceed in order to achieve the goal as efficiently as possible.
Delete the old data
I had tried many approaches in the past, but the simplest one is by far the most efficient. I would like to demonstrate this using the example of my vaccination statistics, where I access the dataset of the German report for the English version. Still I would like to take the visualization as a basis from the original report.
In the first step we need to delete the existing data. This sounds brutal at first, but trust me, the individual visuals will work again later when all tables, columns and measures are named identically.
1) So in the first step, we delete all the tables created by DAX, including the Measure table, directly in Power BI:
2) After that we go to Power Query and delete all remaining tables with Shift and the right mouse button:
3) (Optional) If we are being masochistic and want to bring ourselves close to a panic attack, then we click “Close and Apply” and take a look at the result. It seems that we have destroyed the entire report:
Adding the published dataset
4) Now we need to add the published dataset. To do this, we go under “Get Data” and either search for “Power BI datasets” or go under “Power Platform” and select “Power BI datasets”:
In the next window we select the desired dataset and click on “Create”:
5) The report is now live with the published dataset and miraculously working again. The old data tables are swapped for the published dataset:
Conclusion
There are many ways to replace the underlying database, but simply deleting the data and connecting it to the published dataset is by far the easiest and most efficient way to do this.
