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!

4 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

Leave a Reply

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