How to Create a Copy of an Existing Data Flow for Testing in Power BI Workspaces

Hi Anonymous 

1. Duplicate the Dataflow and Point It to the Test Workbook

• Duplicate the Dataflow:
  • Go to the Power BI workspace and duplicate the existing dataflow.
  • In the new dataflow, edit the query to replace the connection to MainWorkbook.xlsx with TestWorkbook.xlsx (ensure SharePoint paths are correct).
• Verify Schema Consistency:
  • Ensure the schema (column names, data types) of the test file matches the production file exactly.
  • If needed, preprocess the test Excel file to align with the production file.

2. Duplicate the Semantic Model

• Export the existing dataset (semantic model):
  • In Power BI Desktop, connect to the production dataset and save it as a .pbix file.
• Modify the data source in Power BI Desktop:
  • Open the .pbix file, go to Transform Data > Data Source Settings, and change the connection to point to the test dataflow.
  • Ensure all relationships and transformations are preserved during the change.
• Publish the modified .pbix file to a test workspace or a dedicated test environment.

3. Maintain Report Consistency

• Duplicate the reports:
  • In Power BI Service, make a copy of the existing report and connect it to the test dataset.
  • Verify that visuals, measures, and calculations render correctly using the test data.
• Check for errors:
  • Review any broken visuals or calculations caused by schema changes and fix them.

4. Best Practices for Test vs. Production Separation

• Separate Workspaces:
  • Use dedicated workspaces for test and production environments to avoid accidental overlaps.
• Naming Convention:
  • Clearly name your test dataflows, datasets, and reports to distinguish them from production (e.g., Test_Dataflow, Test_SemanticModel, etc.).
• Permissions:
  • Restrict access to the test workspace to ensure changes are controlled.
• Version Control:
  • Maintain a record of changes (e.g., column renaming, DAX updates) to easily replicate them in production after testing.

Additional Recommendations

• Validation Workflow:
  • After testing, validate the changes using test users before applying them to production.
• Refresh Schedules:
  • Set different refresh schedules for test and production pipelines to avoid conflicts.

By following these steps, you can establish a test pipeline that mirrors your production setup while ensuring separation and consistency.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
YouTube: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS