You’d like to compare the Power Query mashups contained in two Microsoft Excel and/or Microsoft Power BI files. Opening Query Editor for each and hand-comparing the queries one at a time quickly becomes tediously painful with all but the smallest of query sets. An easy way to find the differences between mashups in the files would be most helpful.
How about something as simple as:
Compare-DataMashup SomeFile.xlsx OtherFile.pbix
To pull this off, you’ll need Data Mashup Cmdlets and a diff tool. (Have Git installed? You’re all set on the second point because it includes a diff utility.)
Fire up PowerShell Core, drop the below function in it (editing $diffPath, if necessary, and $diffOptions, if desired), and you’re all set.
Important: When you copy and paste the below, you’ll need to change the “&” on line 40 to “&“. (A bug in the syntax highlighter plugin this site uses is causing it to be output to page incorrectly.)
The syntax to use is simple: Compare-DataMashup {first-file} {other-file}
(Note: The two files do not have to be of the same file type: You can compare M code between an Excel file and a Power BI file, if you’d like!)
There you have it! Hope this helps!
Compare-DataMashup
function Compare-DataMashup {
param (
[Parameter(Mandatory=$True)]
[string]$file1,
[Parameter(Mandatory=$True)]
[string]$file2
)
## Begin Config
# Full path to your diff tool
$diffPath = 'C:\Program Files\Git\usr\bin\diff.exe'
# Command-line options which will be passed to the diff tool. Modify per your preferences.
$diffOptions = @(
'--ignore-all-space'
'--color'
'--label=File1'
'--label=File2'
'--unified=3' # the number here controls how many lines of context to display around a $difference
)
## End Config
$queries = @{}
Export-DataMashup $file1 -ErrorAction Stop | ForEach-Object { ($queries[$_.Name] ??= @($null, $null))[0] = $_.Expression }
Export-DataMashup $file2 -ErrorAction Stop | ForEach-Object { ($queries[$_.Name] ??= @($null, $null))[1] = $_.Expression }
try {
$file1QueryTempFile = New-TemporaryFile -ErrorAction Stop
$file2QueryTempFile = New-TemporaryFile -ErrorAction Stop
$queries.GetEnumerator() | Sort-Object Key | ForEach-Object {
$_.Value[0] | Set-Content $file1QueryTempFile -ErrorAction Stop
$_.Value[1] | Set-Content $file2QueryTempFile -ErrorAction Stop
Write-Host "`n======`nQuery: $($_.Key)`n======"
&$diffPath $diffOptions "`"$($file1QueryTempFile)`"" "`"$($file2QueryTempFile)`""
}
}
finally {
Remove-Item $file1QueryTempFile
Remove-Item $file2QueryTempFile
}
}