Power Query M Code Management Best Practice?
The first time I clicked on the Advanced Editor in Power Query my eyes glazed over. What in the world am I even looking at? Once it I realized each line of code corresponded to one step in Power Query Applied Steps things got a lot better - fast.
As I spent more time working on M code a few things stood out to me:
- Working with M Code in the Advanced Editor was a chore -> lots of scrolling
- The code is unformatted -> harder to read, learn and modify
- The code only existed in the PBIX file -> no backup, hard to share with colleagues, and if I wanted to look into something I had to open the PBIX file
Enter Visual Studio Code (VSC). VSC is a free open source code editing tool that has embedded Git, supports debugging, syntax highlighting and intelligent code completion. VSC has a module for Power Query/M Code!
With VSC, I can easily:
- Write native M Code with the aid of intellisense
- Format the code to make it easier to write, read and troubleshoot (and learn!)
- Backup the code to a repository organized into folders
- Share my Power Query M Code with my colleagues via the repository
- Easily review the M Code for all my reports and dataflows in one place
- Leverage M Code 'patterns' to build data models consistently across all my reports
- Ensure my Power Queries are structured the same way for greater consistency
In short, I use VSC to be more consistent and efficient in my Power Query development. This helps reduce the time to prepare and import data into Power BI and build the data model. All with the peace of mind of having my hard work backed up safely. To me, this feels like a best practice.
I highly recommend taking a look at VSC to help you manage your Power Query M Code!
