Data Mashup Cmdlets import and export Power Query from most Microsoft Power BI and Microsoft Excel files; however, they don’t include a GUI editor. Microsoft’s new Power Query / M Language extension for Visual Studio Code provides a graphical editing experience for mashups, with syntax highlighting, validation, and IntelliSense, but doesn’t read or write PBIX, PBIT or XLSX files.
What if the two could be married? Imagine…if this were possible, you could use VSCode to externally edit the Power Query code saved in your Power BI and Excel files….
Now, stop imagining. Try Edit-DataMashup!
To use, you’ll need the Data Mashup Cmdlets installed, VSCode installed and the new Power Query / M Language extension installed.
Next, let PowerShell Core know about Edit-DataMashup. First, figure out whether VSCode is in your path by trying to run code.exe from PowerShell Core. If that opens VSCode, paste the below function definition into the PowerShell terminal window then press enter. If it doesn’t open VSCode, you have two choices: either add code.exe to your path and use the below as-is or make a minor edit to the below before pasting—simply change "code.exe" to the full path of your VSCode install (example: ") then paste and press enter.C:\Users\{username}\AppData\Local\Programs\Microsoft VS Code\Code.exe"
function Edit-DataMashup {
param (
[Parameter(Mandatory=$True, Position=0)]
[string]$File
)
$originalMashup = Export-DataMashup -Raw $File -ErrorAction Stop
$tempFile = New-TemporaryFile -ErrorAction Stop
try {
$tempFile = Rename-Item $tempFile ($tempFile.Name + ".m") -PassThru -ErrorAction Stop
$originalMashup | Out-File $tempFile -NoNewline -ErrorAction Stop
Start-Process "code.exe" "`"$($tempFile)`"" -Wait
$postEditMashup = Get-Content $tempFile -Raw -ErrorAction Stop
if ($originalMashup -eq $postEditMashup) {
$successful = $true
return
}
Import-DataMashup -Raw $File -Experimental -Mashup $postEditMashup -ErrorAction Stop
$successful = $true
}
finally {
if ($successful -ne $true) {
Write-Error "Failed to Save Modified Power Query mashup:`r`n$($postEditMashup)"
}
Remove-Item $tempFile
}
}
With that, you’ll all set! To edit the Power Query from a report or spreadsheet using VSCode, start with VSCode closed then simply run the following (changing “SomeFile.xlsx” to the name of the report or spreadsheet of interest, of course):
Edit-DataMashup SomeFile.xlsx
VSCode will open and you can edit away. When you save and close, the changes you made will be saved back into the report or spreadsheet—unless they contain a syntax error, in which case an error message and your modified mashup will be echoed out to the PowerShell terminal. If you close without saving, the original report or spreadsheet remains unmodified.
Have fun!
Genius! I haven’t tried it yet, but I assume the xlsx/pbix file can’t be open in Excel/Power BI Desktop or the file would be locked for writing, correct?
If the file is open in Excel, you won’t be able to open it using
Edit-DataMashup. Power BI Desktop does its locking a little differently–when it is open,Edit-DataMashupwill be able to open the file but will be unable to save.I just tried it with a PBIX file, everything looks fine and I get a tmp.m file like in your screenshot (though in the AppData > Local > Temp path). I made an edit, saved and closed the file, but the pbix file remains unaltered. Any idea what I might have missed?
Does the PowerShell console window show any error messages?
Hi Ben,
Great name! We named our first son Ben aswell 🙂
This indeed is genius functionality. Is there any way to open multiple files at once so I can can do a search and replace in all files in one go in VS code? I could script this with a loop (I think..) but it would be much nicer if not necessary.
Also, why does it say no support for xlsm when I try to open an xlsm file? If I just rename(not save as) my files to xlsx it works just fine. After making alteration I rename back to xlsm and files work fine.
Hi Athos!
Hm…maybe you could have PowerShell call code.exe once per file, with the first call called so that PowerShell waits for its exit (and probably called using a separate job)….
I can add adding xlsm support to the possible future feature list. 🙂
Support for xlsm files was released this morning!
🙌🏼 Great stuff, that was extremely fast!!
Hi Ben,
Very nice stuff! Unfortunately, as you are aware, this feature is not available when working with the new Power BI files in enhanced dataset format.
Do you happen to know if it is possible (and could you point me in the right direction) to achieve a similar goal (changing/ inserting Power Query code) with the new Power BI files in enhanced dataset format?
Thanks in advance 🙂
For PBIX files: Are you okay with having Power BI open while editing? If so, you could try Tabular Editor. If not, I don’t know of an option. 🙁
On the other hand, PBIT files saved in the new format store various details, including their Power Query expressions, in JSON, so you could try manipulating these files using your favorite text editing tool.
Great information, VERY helpful. Thank you.
I found that I needed to change the invocation of Code from “code.exe” to “code.cmd” for it to automatically launch Code. It might be because I used Chocolatey to install Code.
Thanks a lot Ben! These are awesome news!
Finally, we’re getting closer to a fully featured code editor for Power Query m scripts. I’m excited 🙂
I haven’t gone deeper into how the format is generated. But one improvement to the function that comes to mind just after testing it, is if its possible to generate a different file for each query so you can focus on a particular query?
You could try piping the output of
Export-DataMashup SomeFile.xlsxso that the next pipeline step writes each section member to a separate text file.