An example on how to query data from Power BI using the SQL Server PowerShell module

PowerBI-DataQuery.ps1

Install-Module -Name SqlServer -Scope CurrentUser

$cred = Get-Credential

# Executing a DAX-query to retrieve the property we need in a given scenario
Invoke-ASCmd -Credential $cred -Query "EVALUATE(VALUES(Customers[Id]))" -Server "powerbi://api.powerbi.com/v1.0/myorg/ContosoWorkspace" -Database "CustomerData"

# The returned data is an XML-string, so we can cast the data to an XML-typed variable in PowerShell
[xml]$Data = Invoke-ASCmd -Credential $cred -Query "EVALUATE(VALUES(Customers[Id]))" -Server "powerbi://api.powerbi.com/v1.0/myorg/ContosoWorkspace" -Database "CustomerData"

# And then work with the data as regular XML-objects in PowerShell
$data.return.root.row.Count

# In the example scenario we wanted to find duplicate customer IDs
$Names = @{}
$Duplicates = foreach($row in $Data.return.root.row) {
    if($Names.ContainsKey($row.Customer_Id) -and $Names[$row.Customer_Id] -lt 2) {
        $row
    }
    $Names[$row.Customer_Id] += 1
}

# And call an Azure Logic App in order to notify a team if duplicates is found
if ($Duplicates) {

    Write-Output 'Found duplicates:'
    Write-Output $Duplicates.Customer_Id

    $Body = [PSCustomObject]@{
        Message = "Found duplicates: $($Duplicates.Customer_Id.ToString() -join ',')"
    }

    $NotificationUri = "https://xyz" # Webhook URL to Azure Logic App
    
    $NotificationData = $Body | ConvertTo-Json
    Invoke-RestMethod -Method POST -Uri $NotificationUri -Body $NotificationData -ContentType 'application/json'

}