Unlike software development there is no version control and TFS or Git for Power BI to work multiple data analyst or Power BI engineer on same file at a time. On the other hand, we may need to finish design on time, and more than single Power BI Engineer need to work in same Power BI reporting project at the same time. For such a situation as of now only the way is to work on separate file by multiple designers, divide task accordingly and later on, they have to merge the design manually. Another case: suppose there are two different reports in different workspace, and you are assigned to merge and make single report and publish in one workspace.
This write-up described the way to merge and make final design of report for two files in effective and less time-consuming way. The article elucidates step 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 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 single dataset.
Step 2: Merge the Dataset
Go to the report file where you want to merge to make 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 previous report in 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 that you must copy manually. Add new column or measure and then copy the DAX function or your logic from the original file to Final merge file one by one.
Step 3: Add New Report Page
Add new Page in FinalReport file and give name of this report Page such as Employee Report.
Step 4: Copy Visualization and Paste it to Final Report
Then, you can copy all the visuals of the report including visualizations, table, matrix etc.
Select all visualizations, copy, and paste it 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 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 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 analyst to work in same project, merge their work manually and complete the project within defined timeline. Additionally, we can merge any existing reports into a single report and publish as one report in case of need.
Rijwan Ansari