Images from SharePoint

Loading Images from Sharepoint and displaying them in Power BI using the HTLM VizCreator visuals

Loading images from Sharepoint into native Power BI visuals can be tricky. There’s a simpler way to achieve this with our HTML visuals. This article provides step-by-step instructions.

Step 1:

Start by going to Power Query in your Power BI file and create a new Blank Query.

Step 2:

Use the Advanced Editor to add the following script. Remember to change the path to a SharePoint folder of your choosing.

let
    Source = SharePoint.Files("https://kteamch.sharepoint.com/sites/KTeamPUBLIC", [ApiVersion = 15]),
    #"Filtered Image Location" = Table.SelectRows(Source, each Text.StartsWith([Folder Path], "https://kteamch.sharepoint.com/sites/KTeamPUBLIC/Shared Documents/Online to keep/SharePoint Images to Power BI/")),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Image Location",{"Content", "Name"}),
    //Creates Splitter function
    SplitTextFunction = Splitter.SplitTextByRepeatedLengths(30000),
    //Converts table of files to list
    ListInput = Table.ToRows(#"Removed Other Columns"),
    //Function to convert binary of photo to multiple
    //text values
    ConvertOneFile = (InputRow as list) =>
        let
            BinaryIn = InputRow{0},
            FileName = InputRow{1},
            BinaryText = Binary.ToText(BinaryIn, BinaryEncoding.Base64),
            SplitUpText = SplitTextFunction(BinaryText),
            AddFileName = List.Transform(SplitUpText, each {FileName,_})
        in
            AddFileName,
    //Loops over all photos and calls the above function
    ConvertAllFiles = List.Transform(ListInput, each ConvertOneFile(_)),
    //Combines lists together
    CombineLists = List.Combine(ConvertAllFiles),
    //Converts results to table
    ToTable = #table(type table[Name=text,Pic=text],CombineLists),
    #"Inserted Text After Delimiter" = Table.AddColumn(ToTable, "file type", each Text.AfterDelimiter([Name], ".", {0, RelativePosition.FromEnd}), type text),
    //Adds index column to output table
    AddIndexColumn = Table.AddIndexColumn(#"Inserted Text After Delimiter", "Index", 1, 1)
in
    AddIndexColumn

The primary purpose of this code is to read binary data (like images) from files in a Sharepoint (or any really) folder, convert them into text format, and then organize this data into a structured table with an index. Due to Power BI's character limit per cell, the text is split across multiple rows. The code was borrowed from Chris Webb’s BI blog and can be found on the link here.

Step 3:

Load the table back into Power BI. It should look something like the table below.

Step 4:

Create a new measure with the following code:

Image HTML =
VAR var_file_type =
SWITCH(SELECTEDVALUE(Picture[file type], "png"),
"png", "<img src='data:image/png;base64,",
"jpeg", "<img src='data:image/jpeg;base64,",
"svg", "<img src='data:image/svg+xml;base64,")

RETURN

IF(
HASONEVALUE(Picture[Name]),
var_file_type &
CONCATENATEX(
Picture,
Picture[Pic],
,
Picture[Index],
ASC)
&"'style='max-width:100%;'>"
)

In the variable, we determine the file type of the image (either 'png', 'svg' or ‘jpeg’) and set the appropriate HTML img tag format. If your images are all in the same format, skip the variable and adjust the measure further to not use the variable at all.

In the RETURN part of the code, we first make sure that only one image is selected with HASONEVALUE() and then concatenate the converted text back together with CONCATENATEX() to be able to render a complete image.

As a final step, we add a style attribute to control the image’s maximum width which we have previously determined by a parameter. Width can be specified in pixels or percentages for responsive design. The result is a string that represents the HTML needed to display the selected image with the specified width.

Step 5:

Add either of our HTML visuals (HTML VizCreator Cert or HTML VizCreator Flex) to a Power BI page. If you don’t have the visuals yet, they are available on Microsoft AppSource.

Step 6:

Add the measure you created into the HTML field well and the Name into the Category field well of the visual.

That’s all! Our HTML VizCreator visuals should now render the images in your Sharepoint folder and make them responsive (assuming you used percentages to determine the width of the images).