Unlike software development, there is no version control and TFS or Git for Power BI to work with multiple data analysts or Power BI engineers on the same file at a time. On the other hand, we may need to finish the design on time, and more than a single Power BI Engineer needs to work on the same Power BI reporting project at the same time. For such a situation as of now, only the way is to work on separate files by multiple designers, divide tasks accordingly, and later on, they have to merge the design manually.
Another case - suppose there are two different reports in a different workspace, and you are assigned to merge and make a single report and publish in one workspace.
This write-up will describe the way to merge and make the final design of the report for two files ineffective and less time-consuming way. The article elucidates steps to merge two files of Power BI reports into one single file.
Here we will merge Demoreport.pbix file to FinalReport.pbix file.
Step 1 - Copy Dataset
Open file from where you want to copy and merge to another, for example, DemoReport.pbix
Right-click on Dataset or Table then click on the Edit query option as illustrated below,
Power query editor will be open you can see all the datasets here. Select Dataset or Datasets that you want to move to another file (merging file), then Right-click and Copy all datasets. Note that you can copy single or multiple datasets at a time. All associated/merged/joined datasets will be copied even you select a single dataset.
Step 2 - Merge the Dataset
Go to the report file where you want to merge to make the final file, suppose FinalReport.pbix file in this case. Open this report file and Right-click on Dataset, choose Edit query as depicted.
Then Power query editor will be open, paste the dataset/datasets that you copied from the previous report in the previous step. Once you paste, then click on Close and Apply the changes in your final file.
You can see the copied dataset in your report. However, the measures and New/Custom column cannot be copied; you must copy that manually. Add a new column or measure and then copy the DAX function or your logic from the original file to the Final merge file one by one.
Step 3 - Add New Report Page
Add a new Page in the FinalReport file and give the name of this report Page like Employee Report.
Step 4 - Copy Visualization and Paste it to Final Report
Then, you can copy all the visuals of the report including visualizations, tables, matrix, etc.
Select all visualizations, copy, and paste them to the new report of FinalReport.pbix file (in this case Employee Report page). Copy everything of the designed report including text, cards, visuals, and so on as depicted below,
Now, report merging is completed. You can see all the reports in your single report file. Note that - dataset name should be the same in both the final merge and original file. You should not change the dataset name after copying the dataset from one report to another otherwise your copied visualizations throw an error.
Conclusion
Hence, this article has described the way to merge two Power BI Reports into a single file in an effective way. This helps for two or more Power BI data analysts to work on the same project, merge their work manually and complete the project within a defined timeline. Additionally, we can merge any existing reports into a single report and publish them as one report in case of need.