I saw a blog post recently where Jay Killeen was talking about how he handles his Source Control for the Query Editor or Power Query. And this got me thinking of how I could automate this in a quick and easy manner. So below is how I achieved this.

My solution below, uses a BAT file and 7zip, which means that the process can be automated using a Scheduled Task, and be continuously running in the background.

All that you will need is 7Zip and command prompt (CMD).

Example

For this example, I will show how I get the DataMashup file from your Power BI Desktop file, whilst your Power BI Desktop file is still open.

Then take this file and copy it to OneDrive (You can potentially use this with any other systems such as Google Drive, Drobox etc.) with a Date Timestamp which will make the file unique, as well as know when it was created.

And finally, I will also show how you can revert to a previous version of the file if required.

NOTE: You can also use this process if you want to Share your Power Query/Query Editor files with other people. The people who require the changes must follow the steps in the Reverting back to a Previous Version of the DataMashup File

Requirements

Below you will need to download and install 7Zip (32bit) for this to work.

7Zip (32bit)

What is the Power Query/Query Editor File and where do you find it

I thought it would be good to quickly explain how I came to know about the Power Query/Query Editor file.

  • If you change the file extension of your Power BI Model from .pbix to .zip, this then enables you to view the contents that make up the .pbix file.
  • So as you can see below when I have renamed my .pbix file I see the following:
  • Now after I had been doing some investigations I found out that the file called DataMashup is the file that contains everything in the Power Query/Query Editor.

So this is the file that we need to use for our Source Control for the Power Query/Query Editor.

BAT File explanation

Below is the BAT file that I have created in which it is doing all the steps which are explained below.

NOTE: You will be required to change the following variables in the BAT File, so that it will work for your data.

  • Change this to the location of your Source Directory of your Power BI File

    set SD=”C:\Users\guava\Downloads\”

    • NOTE: I would suggest always encapsulating your Folder and File names with double quotes, so that if there are spaces they will not cause any errors.
  • Change this to the location of your Destination Directory where you want your DataMashup file to be copied to

    set DD=”C:\Users\guava\OneDrive\BI\Power BI\Power Query Versions\Data Gateway Test”

    • NOTE: I would suggest always encapsulating your Folder and File names with double quotes, so that if there are spaces they will not cause any errors.
  • Change this to the Filename of your Power BI Desktop File. NOTE: Do not include the extension.

    set PBIX=”Data Gateway Refresh Test”

    • NOTE: I would suggest always encapsulating your Folder and File names with double quotes, so that if there are spaces they will not cause any errors.

Here below is the code from the BAT file which explains what each step is doing.

The final step is to save the BAT file to an appropriate File Name.

With my example, I gave it the file name of: PBISC – Data Gateway Test.BAT

How it works

Now once you have you updated the BAT file with the correct variables it is as simple as running it from the command line.

NOTE: You have to first save your Power BI Desktop file in order for the changes to be saved into the DataMashup file.

NOTE II: I would recommend that you put in the full File Path for your BAT file to ensure that it will always run
correctly.

  • I created a specific Folder Structure in my OneDrive Folders, so that I have a separate Folder per PBIX file.
    • As you can see it is currently empty.
  • Next I run my BAT file from the command prompt
  • Once it has run I see the following in command prompt:
    • As you can see above it is the output from the BAT File.
    • NOTE: You can also run this BAT File from the Run command, which will also run successfully.
  • And I now see the file in my OneDrive Folder.

Example of Saving Changes

So in the steps below I will now show after I have made a change in the Power Query/Query Editor how the change in both the file name and size is automatically copied and synced to my OneDrive Folder.

  • I have got my Query Editor open in Power BI Desktop
  • What I will now do is to duplicate the table called “Budget Data without New Year“, as well as disable the Loading of this dataset (This is because I want to show how the DataMashup file changes.).
    • As you can see above I have duplicated the table and Disabled the “Enable Load“
  • I then click on Close and Apply, and then save my Power BI Desktop File.
  • Next I run my BAT file again
  • Now you can see in the picture below I have got my new file.
    • You can also see that the file size is slightly larger due to having duplicated
      my table in the Query Editor (which includes all the steps for the table)

Reverting back to a Previous Version of the DataMashup File

The whole reason for the automating the version control is so that in the event you need to go back to a previous version, we have the files required to do this. And I explain how to do this in the steps below.

  • The first thing that you will need to do is close your Power BI Desktop file.
  • Next go to the location of where you have stored the copies of the DataMashup files.
  • With my example, it is in the following location:
  • Now I took a copy of the file that I want to revert back to.
  • With my example I am going to revert back to the first file called DataMashup_02-03-2017_20_08_18 because this is the file that does not have the duplicated table.
  • I made a copy to my Documents Folder.
  • I then rename the file from DataMashup_02-03-2017_20_08_18 to DataMashup
    • NOTE: This is because in the Power BI file format it will look for a file called DataMashup
  • Next I go back to my Power BI Desktop file and rename it from Data Gateway Refresh Test.pbix to Data Gateway Refresh Test.zip
    • It will prompt you asking are you sure you want to change it?
    • Click Yes.
  • Now I went into the zip file by double clicking on the file Data Gateway Refresh Test.zip
  • Next I went back to my Documents folder and copied the DataMashup file.
  • I then went back to my zip file and then right clicked and selected Paste
    • This then prompted me with the following window as shown below.
    • I clicked on Copy and Replace
    • NOTE: This is so that it will replace the DataMashup file with my previous version.
  • Now I had to refresh the zip file to see the changes.
    • Which you can see below the file size has gone from 34k to 27k
  • Now I renamed my zip file back to the pbix file.
    • It will prompt you asking are you sure you want to change it?
    • Click Yes
  • I then opened my Data Gateway Refresh Test.pbix and went into the Query Editor
    • As you can see with the picture below I do not have my additional table
    • It is back to 7 queries.
  • I have successfully reverted to a previous version.

Conclusion

So in conclusion you can see how I automated the process of not only copying the DataMashup file, but also using a system like OneDrive I can automatically sync this data to the cloud and use the built in versioning control.

Here is the link below to the BAT file that I created, for anyone who wants to use it for their own automation and source control for the Power Query/Query Editor data.

I also asked a good friend of mine to create a PowerShell script doing the same thing. So if you want to use PowerShell instead of the BAT File please find the files below.

NOTE: For the BAT file the extension is txt-BAT and for the PowerShell script the extension is txt-ps1, this is so that it should be able to be successfully downloaded or emailed.

PBISC – Data Gateway Test.txt-BAT

Power BI DataMashup.txt-ps1

And as always if anyone has got any comments or suggestions please let me know. As I have a feeling that there might be some great ideas that come from this.