Searching Power Query in Multiple Files

You’re looking for all mashups that touch column “LegacyAmount”…or maybe the task is to find all files whose Power Query uses variable “CalculatedDiscount”…or perhaps you’re trying to locate any M code referencing server names that match the regular expression pattern “SomeServer(0[135]|[2-6]\d)“. Ultimately, you need to search the Power Query mashups in an entire directory (or directory tree) of Microsoft Excel and Microsoft Power BI files. You could open files one at a time and check each by hand…ouch! Is there an easier way to quickly search Power Query across multiple files?

Try Search-DataMashup!

Getting Started

To use, first install Data Mashup Cmdlets, then grab the function below.

Example

Search for xlsx, pbix and pbit Power Queries containing the phrase ‘Source’ (case-insensitive):

Search-DataMashup .\ '*Source*'

Outputs:

MatchingMembers File
--------------- ----
              3 AnotherFile.xlsx
              1 SomeFile.pbix

MatchingMembers reports how many members (e.g. queries) in the given file matched the specified search pattern. For example, a “3” indicates that three mashups in that particular file matched the search pattern at least once. Files with zero matches are omitted from the list that’s outputted.

Perform the same search as above, but include sub-directories (recursively):

Search-DataMashup .\ 'Source' -Recurse

Outputs:

MatchingMembers File
--------------- ----
              3 AnotherFile.xlsx
              1 SomeFile.pbix
              2 ChildDirectory\YetAnotherFile.pbit

Argument Details

• -Path (first argument)—Directory to search.
• -Value (second argument)—Pattern to search for. By default, supports PowerShell wildcards (e.g. same syntax as used with Where-Object's -match). However, if -Regex option is give, this argument is instead interpreted as a regular expression. When using the default option, to search for mashups that contain a string, surround the string in asterisks (e.g. “*Source*”); omitting the asterisks searches for mashups which are equal the specified string.
• -Recurse—Recursively search directory tree starting at -Path (e.g. search sub-directories and sub-sub-directories, etc.). If omitted, only files contained directly in -Path are searched.
• -Regex—Changes -Value from being interpreted as a PowerShell wildcard pattern to being interpreted as a regular expression.
• -CaseSensitive—Causes -Value to be processed as a case-sensitive wildcard pattern or regular expression.

Search-DataMashup

function Search-DataMashup {
    param (
        [Parameter(Mandatory=$True, Position=0)]
        [ValidateScript({
            if(-Not (Test-Path -PathType Container $_) ){
                throw "Folder does not exist"
            }
            return $true
        })]
        [string]$Path,
 
        [Parameter(Mandatory=$True, Position=1)]
        [string]$Value,

        [Parameter(Mandatory=$False)]
        [switch]$Regex,

        [Parameter(Mandatory=$False)]
        [switch]$CaseSensitive,

        [Parameter(Mandatory=$False)]
        [switch]$Recurse
    )
 
    if ($Regex.IsPresent) {
        $Action = $CaseSensitive.IsPresent ? "cmatch" : "match"
    } else {
        $Action = $CaseSensitive.IsPresent ? "clike" : "like"
    }
    $SearchArgs = @{$Action = $true ; Value = $Value}
 
    Get-ChildItem -Recurse:$Recurse $Path -File |
    Where-Object { ".xlsx",".pbix", ".pbit" -eq $_.Extension } |
    ForEach-Object {
        $relativePath = [System.IO.Path]::GetRelativePath(
            [System.IO.Path]::GetFullPath($Path, (Get-Location)),
            $_.FullName
        )

        try {
            $matches = (
                Export-DataMashup -LiteralPath $_.FullName -ErrorAction Stop  |
                Where-Object -Property Expression @SearchArgs
            );

            @{File = $relativePath; Matches = $matches.Count}
        } catch {
            Write-Error "Could not search '$($relativePath)' - $($_)"
        }
    } |
    Where-Object Matches -gt 0 |
    Select-Object @{label='MatchingMembers'; expression={$_.Matches}}, File
}