Memory failure: While attempting to store a string, a string was found that was larger than the page

Hi JB_AT ,

Please check whether the reply in this case helpful?
Memory error - Microsoft Fabric Community

If not, You can try truncate the String in Power Query. For example:

let
    Source = ... ,  // Your data source here
    TruncateThumbnail = Table.TransformColumns(Source, {{"ThumbnailPhoto", each Text.Start(_, 1000), type text}})
in
    TruncateThumbnail

Or you can try compressing the String. For example:

let
    Source = ... ,  // Your data source here
    CompressThumbnail = Table.TransformColumns(Source, {{"ThumbnailPhoto", each Binary.ToText(Text.ToBinary(_, TextEncoding.Base64)), type text}})
in
    CompressThumbnail

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Hi JB_AT ,

Sorry for the late reply, I found a known issue similar to yours, but I'm not sure if you have the same problem. I need to confirm that are you using any calculation groups?

Best Regards,
Dino Tao

string length must less 32KB(32 * 1024 Byte). if the size of picture converted to base64 text is to larger, it should be split into small chunks.

following code domonstration load local image, convert to base64 and split to small chunk

(imagePath as text, optional splitLength as number) => 
let
    limitLength = splitLength ?? 30 * 1024, 
    base64Text = Binary.ToText(File.Contents(imagePath)), 
    base64Length = Text.Length(base64Text), 
    result = List.Generate(
        ()=>0, 
        each _ * limitLength <= base64Length, 
        each _ + 1, 
        each [index = _, substring = Text.Middle(base64Text, _ * limitLength, limitLength)]
    )
in
    result