PowerBI Paginated Reports

One of our clients had a need to display images in a Paginated Report that would vary based on the parameters. In this case it was maps for a particular building that would change from building to building. The client was not confident in technical skills so we decided to explore using a SharePoint List of Images which they could easily update without messing around in SQL.

PowerBI Paginated Reports can now connect to SharePoint easily using PowerQuery so this seemed an obvious solution. As usual, the devil is in the details…

Displaying an Image

This is the easy part. Create an image on the report canvas, and set its properties.

PowerBI Paginated Report Image Properties

 The image source should be “Database”

The MIME type is basically the file type. You can choose bmp, jpeg, gif, png, x-png. Important to note here is you can only have one. While you can set it dynamically, its going to be simpler if you only use the one type – which means you need to be mindful of the type of image you put in your SharePoint list.

The field to use will come from your dataset – we’ll come back to this. 

Creating a SharePoint list

You need the permissions to create the list first. Once you have that just create a new list and give it an ID column (so you can retrieve the image you want) and an Image column (to contain your images). You can add as many columns as you like, such as descriptions and other metadata. For the purposes of the Report you just need an ID and an Image. It should end up something like this:

SharePoint List with Image Column

 

Querying SharePoint to get the image

Now, this is where it needs a little extra thinking. While you can connect to a SharePoint list via PowerQuery, that connection does not (currently) support retrieving images directly from the list. So you need to also query the SharePoint contents to get the binary of the image for display.

This isn’t too hard – you just need two queries in the PowerQuery connection. One niggle I picked up is that the query that retries the image binary has to be the last in the sequence, as the dataset picks up the fields only from the last listed query.

In practice this looks like this, i.e. the query returning the image binaries needs to be last in the sequence:

PowerQuery query sequence

 

A query in 2 parts: Part 1 – the list

From PowerQuery, choose “Get Data” and find SharePoint List. You will connect using your credentials and in order:

• Navigate to the list you want
• Expand the Image column – this exposes the file path through serverRelativeURL and serverURL
• Remove unnecessary columns.

This allows us to retrieve the ID (and other list data if needed) for the image and also its full path in SharePoint

Pseudocode below:

let

  Source = SharePoint.Tables("https://*******.sharepoint.com/sites/******/", [Implementation = "2.0", ViewMode = "All"]),

  #"Navigation 1" = Source{[Id = "{LIST GUID}"]}[Items],

  #"Expanded Site Image" = Table.ExpandRecordColumn(#"Navigation 1", " Image", {"fileName", "serverRelativeUrl", "id", "serverUrl", "thumbnailRenderer"}, {"fileName", "serverRelativeUrl", "id.1", "serverUrl", "thumbnailRenderer"}),

  #"Removed columns" = Table.RemoveColumns(#"Expanded Site Image", {"App Modified By", "App Created By", "Item is a Record", "Label applied by", "Retention label Applied", "Retention label", "Label setting", "Folder Child Count", "Item Child Count", "Type", "Edit", "Attachments", "Version", "Modified By", "Created By", "Created", "Modified", "Content Type", "Asset Name", "Compliance Asset Id", "Color Tag", "thumbnailRenderer", "Portfolio"})

in

  #"Removed columns"

A query in 2 parts: Part 1 – the image

Now we need to retrieve the binary of the image, which means we need to query SharePoint Contents. So again we choose “Get Data” but his time choose SharePoint folder, and in order:

• Navigate to SiteAssets
• Navigate to Lists
• Navigate to the list – for which you’ll need to know the GUID captured in the pseudocode above
• Expand the list
• Convert the Content field to a Binary – this allows the image to be identified in the Paginated Report as image data
• Expand the Attributes to get the file path
• Remove unnecessary columns
• Create the file path column
• Replace any spaces with the HTML annotation “%20”
• Merge it with your first query on file path so you can get the list data
• Expand the merged data
• Remove all unnecessary columns
• Filter out any blanks / nulls in case of bad data in your list

Pseudocode below: 

let

  Source = SharePoint.Contents("https://********.sharepoint.com/sites/******/"),

  #"Navigate to Site Assets" = Source{[Name = "SiteAssets"]}[Content],

  #"Navigate to Lists" = #"Navigate to Site Assets"{[Name = "Lists"]}[Content],

  #"Expand List" = #"Navigate to Lists"{[Name = "934c43b3-cbe1-4aa1-9398-1c79cee46189"]}[Content],

  #"Added custom" = Table.AddColumn(#"Expand List", "BinaryOfSiteImg", each Binary.From([Content])),

  #"Expanded Attributes 1" = Table.ExpandRecordColumn(#"Added custom", "Attributes", {"Content Type", "Kind", "Size", "ContentTypeID", "Name", "ColorTag", "ComplianceAssetId", "Title", "Description", "ExtractedText", "Id", "ContentType", "Created", "CreatedById", "Modified", "ModifiedById", "CopySource", "ApprovalStatus", "Path", "CheckedOutToId", "VirusStatus", "IsCurrentVersion", "Owshiddenversion", "Version", "CreatedBy", "ModifiedBy", "CheckedOutTo"}, {"Content Type", "Kind", "Size", "ContentTypeID", "Name.1", "ColorTag", "ComplianceAssetId", "Title", "Description", "ExtractedText", "Id", "ContentType", "Created", "CreatedById", "Modified", "ModifiedById", "CopySource", "ApprovalStatus", "Path", "CheckedOutToId", "VirusStatus", "IsCurrentVersion", "Owshiddenversion", "Version", "CreatedBy", "ModifiedBy", "CheckedOutTo"}),

  #"Removed columns" = Table.RemoveColumns(#"Expanded Attributes 1", {"Date accessed", "Date modified", "Date created"}),

  #"Create File Path" = Table.AddColumn(#"Removed columns", "Merged", each Text.Combine({[Path],"/", [Name.1]}), type text),

  #"Convert to Relative Path" = Table.ReplaceValue(#"Create File Path", " ", "%20", Replacer.ReplaceText, {"Merged"}),

  #"Merged queries" = Table.NestedJoin(#"Convert to Relative Path", {"Merged"}, List, {"serverRelativeUrl"}, "List", JoinKind.LeftOuter),

  #"Expanded List" = Table.ExpandTableColumn(#"Merged queries", "List", {"Id"}, {"Id"}),

  #"Removed columns 1" = Table.RemoveColumns(#"Expanded List", {"Content", "Name", "Extension", "Content Type", "Kind", "Size", "ContentTypeID", "Name.1", "ColorTag", "ComplianceAssetId", "Title", "Description", "ExtractedText", "Id", "ContentType", "Created", "CreatedById", "Modified", "ModifiedById", "CopySource", "ApprovalStatus", "Path", "CheckedOutToId", "VirusStatus", "IsCurrentVersion", "Owshiddenversion", "Version", "CreatedBy", "ModifiedBy", "CheckedOutTo", "Folder Path", "Merged"}),

  #"Filtered rows" = Table.SelectRows(#"Removed columns 1", each [Id] <> null and [Id] <> "")

in

  #"Filtered rows"

Which should leave you with a neat table like this:

Final Query output

 

Now display your image

Now we go back to our Image properties and set the expression for your Image:

Expression in Paginated Report to retrieve image

 

And you are now successfully showing images from a SharePoint List in your Paginated Report!

Conclusion

Displaying Images from a SharePoint list in a Paginated Report requires a few simple steps:

• Creating a SharePoint list
• Using PowerQuery to get the image path and attributes from the List
• Using a second query to get the image binary and path
• Joining the two queries together to get image binary and attributes