Editing Report/Spreadsheet Mashups in VSCode

, , , , ,

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!

Screenshot showing VSCode being used to edit Power Query from a report file

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: "C:\Users\{username}\AppData\Local\Programs\Microsoft VS Code\Code.exe") then paste and press enter.

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!

10 thoughts on “Editing Report/Spreadsheet Mashups in VSCode

    1. Ben Gribaudo Post author

      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-DataMashup will be able to open the file but will be unable to save.

      Reply
  1. Olivier Travers

    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?

    Reply
  2. Athos

    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.

    Reply
    1. Ben Gribaudo Post author

      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. 🙂

      Reply
  3. Gerard

    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 🙂

    Reply
    1. Ben Gribaudo Post author

      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.

      Reply

Leave a Reply to Ben Gribaudo Cancel reply

Your email address will not be published. Required fields are marked *