Create your own Data Model source file

I saw someone asking in the Forms about getting a data source Excel file for a particular showcase which did not have one supplied as a download and thought i would share recipe for creating your own

As long as you have the Power BI .PBIX file you can re create the Excel Data model file from the contents of the .PBIX.

In the Showcases i was particularly drawn to the US Regional Performance which uses calculations based on Dynamic Metrics

The Excel data model source was not available in the downloads so i set about creating my own Excel Source by exporting the data in your PBIX to CSV files for each table than converting them to Excel tables which i combined into a single Excel source file.

The steps involved are

1.  For each of the Core tables in the PBIX create a Table Visual and include each element of that Core table

2.  Export the Table to a .CSV file

3.  Save the .CSV as an Excel Workbook

4.  Repeat steps 1 , 2 and 3 for each core table  

     ( note you do not do this for Calculated tables in the model or supporting grouping tables which are created using the enter Data option, you would create those as part of your build process )

5.   Convert each of the Excel Workbooks to an Excel Table

6 .  Combine the separate Excel tables into a single Excel File which represents the data model to be imported

7.   Analyse the existing Completed PBIX model to understand which transformation steps were applied and note changes that were applied from the original data import.  Things such as  Calculated Columns , Groupings on data values  etc.

At this stage you can start the modelling from ground zero using the Excel Data source

The attached Winrar file contains

– A word document describing the build process for the US Regional Performance Power BI template

  It describes the data import and the steps applied to the data after import to arrive at the final model

– text file with code snippets for the DAX Calculations

– Two Power BI .pbix templates representing the build of the model from start to finish

    I like to develop a model in stages as the file can then be re used as a template for other cases.

US Regional Performance Build.rar (1.6 MB)

For anyone interested. There are more options that can be used to export data from a Power BI Model… Avi Singh made an overview you can check that video here:

1 Like

Hi @russellw999, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!