Hi @JB_AT
When you retrieve the ThumbnailPhoto attribute from Active Directory, it is usually stored as a binary object, not a string, because it represents image data. If you’re seeing an error, it's often because the photo data is too large to be processed directly as text. Here are some steps you can take in Power Query or Power BI to handle this issue:
1. Use Binary Format for ThumbnailPhoto
If you're not already doing so, ensure that you’re importing the ThumbnailPhoto attribute as binary data rather than as a text string. This will reduce the load on Power BI, as binary data is more efficient for handling large objects like images.
To change the format:
- In Power Query, find the column with ThumbnailPhoto.
- Right-click on the column header and select Change Type > Binary.
2. Split the Data into Chunks (if necessary)
If Power BI or Power Query continues to struggle with large binary objects, you can split the data into smaller chunks. However, handling binary data directly as chunks can be challenging in Power BI.
Another approach is to limit the photo size at the source by setting a maximum thumbnail size in Active Directory. You can use a tool like PowerShell to resize or compress large thumbnails before bringing them into Power BI.
3. Resize the Thumbnail in Active Directory (using PowerShell)
If you have control over the Active Directory environment, you can use PowerShell to resize and compress the images in the ThumbnailPhoto attribute to keep them below the 10 KB limit.
Here’s a PowerShell script example to resize and compress images in Active Directory:
# Define the size limit (e.g., 10 KB) and resize dimensions
$MaxFileSize = 10240 # 10 KB in bytes
$ThumbnailWidth = 96
$ThumbnailHeight = 96
# Function to resize images
Function Resize-Thumbnail {
param (
[byte[]]$OriginalPhoto
)
# Load the image from the byte array
$Stream = New-Object IO.MemoryStream(,$OriginalPhoto)
$Image = [System.Drawing.Image]::FromStream($Stream)
# Resize image
$Thumbnail = $Image.GetThumbnailImage($ThumbnailWidth, $ThumbnailHeight, $null, [IntPtr]::Zero)
# Save resized image to a new memory stream
$ResizedStream = New-Object IO.MemoryStream
$Thumbnail.Save($ResizedStream, [System.Drawing.Imaging.ImageFormat]::Jpeg)
# Return the resized image as a byte array
return $ResizedStream.ToArray()
}
# Import Active Directory module
Import-Module ActiveDirectory
# Get all users with ThumbnailPhoto
$Users = Get-ADUser -Filter * -Properties ThumbnailPhoto
foreach ($User in $Users) {
if ($User.ThumbnailPhoto -and ($User.ThumbnailPhoto.Length -gt $MaxFileSize)) {
# Resize the thumbnail if it exceeds the size limit
$NewPhoto = Resize-Thumbnail -OriginalPhoto $User.ThumbnailPhoto
# Update the user's thumbnail in Active Directory
Set-ADUser -Identity $User.SamAccountName -Replace @{ThumbnailPhoto = $NewPhoto}
}
}
4. Store Photos Externally and Reference URLs
Another option, if feasible, is to store the photos externally (e.g., in SharePoint, Azure Blob Storage, or a similar service), then store the URL in Active Directory or another system. This way, you can load only the URL in Power BI, and display the image using a web image URL, which can reduce the size and complexity of the data.
5. Limit Data Imported to Power BI
If you're bringing all user records into Power BI but only need a subset of photos, consider filtering the records at the data source level (using filters in your AD connector query) to limit the number of rows with thumbnail photos being retrieved.
These approaches should help to manage the issue of overly large data in Power BI by either compressing the images at the source, reducing the data imported, or referencing external image storage.
Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂
Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
