Managing Queries in PowerBI and Power Query using Git

PowerBI and Power Query are becoming powerful tools I use for everyday data analysis and is quickly replacing Excel. Previously I would spend almost all my time in Excel/VBA performing my data analysis. Particularly with tools including Pivot Tables, PowerPivot and Structured Table Referenced summation.

M-lang has revolutionised this with it's modern take on structured data analysis.

And with modern languages and modern applications such as PowerBI and Power Query you should be safe to assume there would be modern tooling. You would be wrong. With a focus on end user consumption of information, PowerBI and it's M-Lang fall short of giving the developers a modern IDE, any form of version control and a weak copy/paste system if you want to share queries across workbooks or perform backups.

So below is my workflow for filling these gaps and the frustrations I experience when doing this.

For my version control system I use Git
For my cloud repository I use Github
For my Windows based command line I use GitBash
For my Text Editor I use Github's Atom (running on ElectronJS)
I make sure to have my Git configuration file in place as GitFlow is unavailable on Windows (or at least I haven't figured it out)

I'll be sure to create a new directory for each project and initialise Git within that directory. I'll add to that directory a .pbix or .xlsm (if I am using PowerQuery) and then structure the directory as follows:

• params [Group]
  • PATH.m
  • ProjectName.m
  • ThisFinancialYear.m
  • LastUpdatedOn.m
• db [Group]
  • DW.m
  • SFDC.m
• models [Group]
  • Date.m
  • Customer.m
  • Sales.m
  • Product.m
• staging [Group]
  • ProductByFinancialYearVariance.m

I'll also create this directory structure inside the Query Editor of my PowerBI or Power Query file.

Life then becomes a matter of:

Copy from Explorer and Paste into Query Editor
Make Changes in the PowerBI Query Editor
Copy from the Query Editor and Paste into Explorer

  git add .
  git commit -m "Made change x"
  git push origin master

By following this pattern I am sure to avoid a few issues including:

• PowerBI crashing and losing work (countless times this has occured before I implemented this)
• Having poor visibility over changes to .m files
• Potentially being able to develop with a group of people
• Easily share queries across workbooks by creating .pbix template files in a directory I call MSBIDataCatalog
• I can use a develop/master branch Git workflow to work on changes to files that are published to the PowerBI.com without losing my ability to refresh the data (simple git checkout master, open the file, refresh, publish, save, git checkout develop and go back to working on improvements)

Things that would make this easier:

1. Allow queries to be imported/exported 'the right way'. Currently only supports Copy/Paste which is rubbish
2. Allow an external IDE to be used or improve the IDE of PowerBI (because honestly, I doubt their developers would put up with it if it was their day job)
3. Implement a version control system like Git to allow push/pull/clone/fork of queries from Github repositories (save using the poorly implemented DataCatalog that currently comes with Azure (only accessible on Power Query and not PowerBI)

START RANT

I know a lot of changes are in the works and I am very happy with the monthly release cycle. I don't believe many people in the community are using modern version control systems, no one supports the ideas on the UserVoice site or they already have a better way than I do to do all of this.

END RANT

I'd be interested to hear from others if you are using a version control system (even if it is a timestamp copy of the file) or if you have any advice on management of queries across workbooks. Really struggling to hit a state of flow in my PowerBI development workflow.

Similar