<# .SYNOPSIS Lists Power BI queries that connect to Databricks and shows which driver each one uses: ADBC (Implementation="2.0") or the legacy ODBC driver. Flags semantic models that mix both. .DESCRIPTION Read-only. The script opens files for reading only and never changes a file, a report or a semantic model. The only thing it writes is the optional CSV you ask for with -OutCsv. Two ways to run it: 1) Local files (no sign-in, nothing leaves your machine): .\find-databricks-odbc.ps1 -Path "C:\PowerBI" Scans PBIP projects (*.tmdl, model.bim), loose *.m / *.pq files, and *.pbix / *.pbit files. For .pbix files it reads the embedded Power Query package (DataMashup). Some .pbix files keep their queries only inside the compressed data model; those are reported as "not readable", and the fix is to open the file in Power BI Desktop and save it as a PBIP project. 2) Whole tenant, through the Power BI admin scanner API (read-only admin APIs): .\find-databricks-odbc.ps1 -Tenant Needs: the MicrosoftPowerBIMgmt PowerShell module, a Fabric administrator account (or a service principal allowed to use read-only admin APIs), and the two tenant settings "Enhance admin APIs responses with detailed metadata" and "Enhance admin APIs responses with DAX and mashup expressions". Scans up to 100 workspaces per call and respects the documented rate limits. Why it matters: since 15 Oct 2026 ADBC is the default driver for NEW Databricks connections in Power BI, but existing queries stay on ODBC until someone sets Implementation="2.0". The docs also say every Databricks connection in one semantic model must use the same driver. Source: https://learn.microsoft.com/en-us/azure/databricks/partners/bi/power-bi/adbc .PARAMETER Path Folder to scan recursively. Defaults to the current folder. .PARAMETER Tenant Scan every shared workspace in the tenant through the admin scanner API instead of local files. .PARAMETER OutCsv Optional path for a CSV copy of the results. .NOTES Octelion Data & AI Solutions, https://octelion.dev/guides/ Version 1.0, 21 Oct 2026. Works in Windows PowerShell 5.1 and PowerShell 7. Provided as is; read it before you run it. #> [CmdletBinding(DefaultParameterSetName = 'Local')] param( [Parameter(ParameterSetName = 'Local')] [string]$Path = '.', [Parameter(ParameterSetName = 'Tenant', Mandatory = $true)] [switch]$Tenant, [string]$OutCsv ) Set-StrictMode -Version 2.0 $ErrorActionPreference = 'Stop' # Databricks connector functions in Power Query (Azure Databricks and the multi-cloud Databricks connector). $ConnectorPattern = '(?\b(?:Databricks|DatabricksMultiCloud|AzureDatabricks)\.(?:Catalogs|Query|Contents))\s*\(' function Get-CallText { # Returns the text of a function call from its opening parenthesis to the matching close, # ignoring parentheses inside M string literals ("..." with "" as an escaped quote). param([string]$Text, [int]$OpenIndex) $depth = 0; $inString = $false for ($i = $OpenIndex; $i -lt $Text.Length; $i++) { $c = $Text[$i] if ($c -eq '"') { $inString = -not $inString; continue } if ($inString) { continue } if ($c -eq '(') { $depth++ } elseif ($c -eq ')') { $depth--; if ($depth -eq 0) { return $Text.Substring($OpenIndex, $i - $OpenIndex + 1) } } } return $Text.Substring($OpenIndex) } function Find-DatabricksCalls { # Finds every Databricks connector call in a block of M text and classifies its driver. param([string]$Text, [string]$Model, [string]$Source, [string]$DefaultLocation) $results = @() if ([string]::IsNullOrEmpty($Text)) { return $results } foreach ($m in [regex]::Matches($Text, $ConnectorPattern)) { $open = $m.Index + $m.Length - 1 $call = Get-CallText -Text $Text -OpenIndex $open $impl = [regex]::Match($call, 'Implementation\s*=\s*"([^"]*)"') $implValue = '(not set)' if ($impl.Success) { $implValue = $impl.Groups[1].Value } $driver = 'ODBC (legacy)' if ($implValue -eq '2.0') { $driver = 'ADBC' } # Nearest name above the call: TMDL table/partition, model.bim name, or a shared query in Section1.m. $before = $Text.Substring(0, $m.Index) $location = $DefaultLocation $names = [regex]::Matches($before, '(?m)^\s*(?:table|partition|expression)\s+(?.+?)\s*(?:=.*)?$|(?m)^\s*shared\s+(?#?"?[^=]+?"?)\s*=') if ($names.Count -gt 0) { $location = $names[$names.Count - 1].Groups['n'].Value.Trim() } $line = ([regex]::Matches($before, "`n")).Count + 1 $results += [pscustomobject]@{ SemanticModel = $Model Location = $location Connector = $m.Groups['fn'].Value Implementation = $implValue Driver = $driver ModelStatus = '' Source = $Source Line = $line } } return $results } function Read-TextShared { # Read without locking the file, so it is safe on files open in Power BI Desktop. param([string]$File) $fs = [System.IO.File]::Open($File, 'Open', 'Read', 'ReadWrite') try { $sr = New-Object System.IO.StreamReader($fs, $true); return $sr.ReadToEnd() } finally { $fs.Dispose() } } function Get-ModelName { param([string]$File) $dir = Split-Path -Parent $File while ($dir) { $leaf = Split-Path -Leaf $dir if ($leaf -match '\.(SemanticModel|Dataset)$') { return ($leaf -replace '\.(SemanticModel|Dataset)$', '') } $parent = Split-Path -Parent $dir if ($parent -eq $dir) { break } $dir = $parent } return [System.IO.Path]::GetFileNameWithoutExtension($File) } function Get-PbixMashup { # DataMashup layout (MS-QDEFF): 4-byte version, 4-byte package length, then a zip package # that holds Formulas/Section1.m. Everything is read in memory; the .pbix is never modified. param([string]$File) Add-Type -AssemblyName System.IO.Compression $fs = [System.IO.File]::Open($File, 'Open', 'Read', 'ReadWrite') try { $outer = New-Object System.IO.Compression.ZipArchive($fs, [System.IO.Compression.ZipArchiveMode]::Read) $entry = $outer.GetEntry('DataMashup') if (-not $entry) { return $null } $ms = New-Object System.IO.MemoryStream $s = $entry.Open(); $s.CopyTo($ms); $s.Dispose() $bytes = $ms.ToArray() if ($bytes.Length -lt 8) { return $null } $len = [BitConverter]::ToInt32($bytes, 4) if ($len -le 0 -or (8 + $len) -gt $bytes.Length) { return $null } $buf = New-Object byte[] $len [Array]::Copy($bytes, 8, $buf, 0, $len) $pkg = New-Object System.IO.MemoryStream(, $buf) $inner = New-Object System.IO.Compression.ZipArchive($pkg, [System.IO.Compression.ZipArchiveMode]::Read) $m = $inner.GetEntry('Formulas/Section1.m') if (-not $m) { return $null } $sr = New-Object System.IO.StreamReader($m.Open()) try { return $sr.ReadToEnd() } finally { $sr.Dispose() } } finally { $fs.Dispose() } } function Join-Expression { # model.bim and the scanner API store expressions as a string or an array of lines. param($Value) if ($null -eq $Value) { return '' } if ($Value -is [System.Array]) { return ($Value -join "`n") } return [string]$Value } function Scan-Local { param([string]$Root) $all = @() $files = Get-ChildItem -LiteralPath $Root -Recurse -File -ErrorAction SilentlyContinue | Where-Object { $_.Extension -in '.tmdl', '.m', '.pq', '.pbix', '.pbit' -or $_.Name -eq 'model.bim' } foreach ($f in $files) { $model = Get-ModelName $f.FullName try { if ($f.Extension -in '.pbix', '.pbit') { $text = Get-PbixMashup $f.FullName if ($null -eq $text) { $all += [pscustomobject]@{ SemanticModel = $model; Location = '-'; Connector = '-'; Implementation = '-' Driver = 'not readable'; ModelStatus = 'Open in Desktop and save as PBIP, or use -Tenant'; Source = $f.FullName; Line = 0 } continue } $all += Find-DatabricksCalls -Text $text -Model $model -Source $f.FullName -DefaultLocation 'Section1.m' } elseif ($f.Name -eq 'model.bim') { $json = (Read-TextShared $f.FullName) | ConvertFrom-Json foreach ($t in @($json.model.tables)) { foreach ($p in @($t.partitions)) { if ($p -and $p.source -and ($p.source.PSObject.Properties.Name -contains 'expression')) { $all += Find-DatabricksCalls -Text (Join-Expression $p.source.expression) -Model $model -Source $f.FullName -DefaultLocation $t.name } } } if ($json.model.PSObject.Properties.Name -contains 'expressions') { foreach ($e in @($json.model.expressions)) { $all += Find-DatabricksCalls -Text (Join-Expression $e.expression) -Model $model -Source $f.FullName -DefaultLocation $e.name } } } else { $all += Find-DatabricksCalls -Text (Read-TextShared $f.FullName) -Model $model -Source $f.FullName -DefaultLocation $f.BaseName } } catch { Write-Warning "Skipped $($f.FullName): $($_.Exception.Message)" } } return $all } function Scan-Tenant { if (-not (Get-Module -ListAvailable -Name MicrosoftPowerBIMgmt.Profile)) { throw 'Install the module first: Install-Module MicrosoftPowerBIMgmt -Scope CurrentUser' } Import-Module MicrosoftPowerBIMgmt.Profile Connect-PowerBIServiceAccount | Out-Null # GetModifiedWorkspaces: read-only, max 30 calls an hour; one call lists every workspace. $ws = (Invoke-PowerBIRestMethod -Method Get -Url 'admin/workspaces/modified?excludePersonalWorkspaces=True' | ConvertFrom-Json) $ids = @($ws | ForEach-Object { $_.id }) Write-Host "Workspaces to scan: $($ids.Count)" $all = @() for ($i = 0; $i -lt $ids.Count; $i += 100) { $batch = $ids[$i..([Math]::Min($i + 99, $ids.Count - 1))] $body = @{ workspaces = $batch } | ConvertTo-Json $scan = Invoke-PowerBIRestMethod -Method Post -Url 'admin/workspaces/getInfo?datasetExpressions=True&datasourceDetails=True' -Body $body | ConvertFrom-Json do { Start-Sleep -Seconds 5 $status = (Invoke-PowerBIRestMethod -Method Get -Url "admin/workspaces/scanStatus/$($scan.id)" | ConvertFrom-Json).status } while ($status -notin 'Succeeded', 'Failed') if ($status -eq 'Failed') { Write-Warning "Scan $($scan.id) failed"; continue } $result = Invoke-PowerBIRestMethod -Method Get -Url "admin/workspaces/scanResult/$($scan.id)" | ConvertFrom-Json foreach ($w in @($result.workspaces)) { if (-not ($w.PSObject.Properties.Name -contains 'datasets')) { continue } foreach ($d in @($w.datasets)) { $model = "$($w.name) / $($d.name)" if ($d.PSObject.Properties.Name -contains 'tables') { foreach ($t in @($d.tables)) { if (-not ($t.PSObject.Properties.Name -contains 'source')) { continue } foreach ($s in @($t.source)) { $all += Find-DatabricksCalls -Text (Join-Expression $s.expression) -Model $model -Source "workspace $($w.id)" -DefaultLocation $t.name } } } if ($d.PSObject.Properties.Name -contains 'expressions') { foreach ($e in @($d.expressions)) { $all += Find-DatabricksCalls -Text (Join-Expression $e.expression) -Model $model -Source "workspace $($w.id)" -DefaultLocation $e.name } } } } } return $all } if ($PSCmdlet.ParameterSetName -eq 'Tenant') { $rows = @(Scan-Tenant) } else { $rows = @(Scan-Local -Root (Resolve-Path -LiteralPath $Path).Path) } # One driver per semantic model: flag models that mix ADBC and ODBC. foreach ($g in ($rows | Where-Object { $_.Driver -in 'ADBC', 'ODBC (legacy)' } | Group-Object SemanticModel)) { $drivers = @($g.Group | Select-Object -ExpandProperty Driver -Unique) $status = 'All ODBC: migrate' if ($drivers.Count -gt 1) { $status = 'MIXED: not supported, align all connections' } elseif ($drivers[0] -eq 'ADBC') { $status = 'All ADBC: done' } foreach ($r in $g.Group) { $r.ModelStatus = $status } } $found = @($rows | Where-Object { $_.Driver -in 'ADBC', 'ODBC (legacy)' }) if ($found.Count -eq 0) { Write-Host 'No Databricks connections found.' } $rows | Sort-Object SemanticModel, Location | Format-Table SemanticModel, Location, Driver, Implementation, ModelStatus -AutoSize | Out-Host $summary = $found | Group-Object SemanticModel | ForEach-Object { $_.Group[0].ModelStatus } | Group-Object | ForEach-Object { "{0}: {1} model(s)" -f $_.Name, $_.Count } if ($summary) { Write-Host 'Summary'; $summary | ForEach-Object { Write-Host " $_" } } if ($OutCsv) { $rows | Export-Csv -LiteralPath $OutCsv -NoTypeInformation -Encoding UTF8; Write-Host "Saved $OutCsv" }