Creating Data Quality Controls on Power BI Datasets using DAX

Data quality controls are essential to ensure the accuracy and reliability of the insights derived from your Power BI reports.

This guide shows how to connect to Power BI datasets to either to data quality controls or to compare to other data sources.

1. Create an App Registration

   Create an app registration in Azure and add under Global Parameters in TimeXtender Data Quality under General > Configurations
   Make sure to check Encrypt for Secret so it’s not visible in logs.
    

   

    

2. Find groupid and dataset id in 

   Navigate to the dataset in app.powerbi.com and find the groupid and datasetid in the address bar.
    

   

    

3. Create a Query Snippet

   Under Data Quality > Tests open Query Snippets and add a new snippet: PowerBIQuery with this code:
    

   # Replace with your values
   $appId = "{$CSSAppReg_ClientId}"
   $appSecret = "{$CSSAppReg_Secret}"
   $tenantId = "{$CSSAppReg_TenantId}"
   
   
   
   # Token URL
   $tokenUrl = "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token"
   
   # Request body
   $body = @{
       grant_type = "client_credentials"
       client_id = $appId
       client_secret = $appSecret
       scope = "https://analysis.windows.net/powerbi/api/.default"
   }
   
   # Get access token
   $response = Invoke-RestMethod -Uri $tokenUrl -Method Post -Body $body
   $accessToken = $response.access_token
   
   
     # Endpoint URL (replace groupId and datasetId with your values)
   $endpointUrl = "https://api.powerbi.com/v1.0/myorg/groups/$groupId/datasets/$datasetId/executeQueries"
   
   # Headers
   $headers = @{
      Authorization = "Bearer $accessToken"
       "Content-Type" = "application/json"
   }
   
   # DAX Query Body
   $daxQueryBody = @"
   {
     "queries": [
       {
         "query": "$evaluate"
       }
     ],
     "serializerSettings": {
       "includeNulls": true
     }
   }
   "@
   
   # Execute API call
   $apiResponse = Invoke-RestMethod -Uri $endpointUrl -Method Post -Headers $headers -Body $daxQueryBody
   $PowerBIResults = $apiResponse.results.tables[0].rows 
   

4. Create a Query

   Create a new Query under Data Quality > Tests
   Here is an example code that fetches all entries with LineTotal < 1 and Quantity = 42

   $groupId = "cab20807-dc6f-4bf5-8144-dabfab21c934"
   $datasetId = "3c36052e-daf8-4eff-864b-b5c4f568f877"
   $evaluate = "EVALUATE FILTER('Sheet', 'Sheet'[LineTotal] < 1)"
   
   {PowerBIDataSetQuery}
   
   
   $PowerBIResults | Where-Object { $_."Sheet[Quantity]" -eq 42 }

5. Workaround for versions prior to 25.2
   In earlier versions of TimeXtender Data Quality it doesn’t recognize columns with [ ] in their name and Expressions in PowerShell are also not correctly parsed.
   A workaround is to:
   1. Define a new Query Snippet that handles the output better and call it PowerShellFunctions

   1. 
      
      function exds-Get-Type 
      { 
          param($type) 
      
      $types = @( 
      'System.Boolean', 
      'System.Byte[]', 
      'System.Byte', 
      'System.Char',  'System.Datetime', 
      'System.Decimal', 
      'System.Double', 
      'System.Guid', 
      'System.Int16', 
      'System.Int32', 
      'System.Int64', 
      'System.Single', 
      'System.UInt16', 
      'System.UInt32', 
      'System.UInt64') 
      
          if ( $types -contains $type ) { 
              Write-Output "$type"
          } 
          else { 
              Write-Output 'System.String' 
      
          } 
      } #Get-Type 
      
      
      function exds-Out-DataTable
      {
          [CmdletBinding()]
          param([Parameter(Position = 0, Mandatory =$true, ValueFromPipeline = $true)] [PSObject[]]$InputObject) 
      
      Begin 
      { 
          $dt = new-object Data.datatable
          $First = $true  
      } 
      Process 
      { 
          foreach ($object in $InputObject) 
          { 
              $DR = $DT.NewRow()   
              foreach($property in $object.PsObject.get_properties()) 
              {   
              	$columnName = $property.Name.ToString() -replace '\.', '_'
                  if ($First) {
                          $Col = New-Object Data.DataColumn
                          $Col.ColumnName = $columnName
                          if ($property.Value) {
                              if ($property.Value -isnot [System.DBNull]) {
                                  $Col.DataType = [System.Type]::GetType($(exds-Get-Type $property.TypeNameOfValue))
                              }
                          }
                          $DT.Columns.Add($Col)
                      }
                      if ($property.GetType().IsArray) {
                          $DR.Item($columnName) = $property.Value | ConvertTo-XML -As String -NoTypeInformation -Depth 1
                      } else {
                          $DR.Item($columnName) = $property.Value
                      }
              }   
              $DT.Rows.Add($DR)   
              $First = $false 
          } 
      }  
      
      End 
      { 
         # Write-Output @(, ($dt)) 
         Write-Output $dt
      } 
      
      }

      2. Refer to it in the PowerShell Query, rename the columns and pipe the results through our helper function. Finally remove the extra columns in Column Formatting.

   2. $groupId = "cab20807-dc6f-4bf5-8144-dabfab21c934"
      $datasetId = "3c36052e-daf8-4eff-864b-b5c4f568f877"
      $evaluate = "EVALUATE FILTER('Sheet', 'Sheet'[LineTotal] < 1)"
      
      {PowerBIDataSetQuery}
      {PowerShellFunctions}
      
      $PowerBIResults | Where-Object { $_."Sheet[Quantity]" -eq 42 }| Select-Object @{ Name="SalesOrderId"; Expression={$_."Sheet[SalesOrderId]"}},@{ Name="Quantity"; Expression={$_."Sheet[Quantity]"}},@{ Name="LineTotal"; Expression={$_."Sheet[LineTotal]"}} | exds-Out-DataTable

Best Practices

In the example we are both filtering results using DAX Evaluate and using Where-Object in the PowerShell.

The DAX Evaluate is executed on the Power BI service and therefore is very quick when working with a lot of data.

When using Where-Object you are downloading all the rows and then doing filtering afterwards and can therefore be slow and take up uneccesary resources. However, doing the data manipulation in PowerShell can allow for more complex manipulation. Therefore, be mindful of where to have the filtering logic.