Comparing M Code Between Files

, , ,

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
Screenshot showing the comparison of a query between two files:
======
Query: Employee
======
--- File1
+++ File2
@@ -1,4 +1,6 @@
 let
-    Source = #table(null, {{"Tom", 15, true}})
+    Source = #table(null, {{"Tom", 15, true}}),
+    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", Currency.Type}, {"Column3", type logical}}),
+    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Name"}, {"Column2", "Rate"}, {"Column3", "Active"}})
 in
-    Source
+    #"Renamed Columns"

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.

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
    }
}

Leave a Reply

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