cancel
Showing results for 
Search instead for 
Did you mean: 

Include Custom Icons in Your Tables

Did you know you can put icons directly in your report's table? Well, it turns out you can! And here is how you do it. 

 

Basics

Let's assume you have your icons ready to go, they have been uploaded to a server, no authentication needed, and they aren't going to change unless you update the report.

Adding the link to the image to the report

In the Power Query Editor, click Enter Data, then fill in the tableIn the Power Query Editor, click Enter Data, then fill in the table

In the Power query editor, enter in two columns, one for "Icon Name" and another for "Icon Url". Fill in the data with your urls. Once you are finished Close and Load.

Creating the visual

Start by adding a table or matrix visual  with a column for "Icon Name". 

Table with the name of the iconsTable with the name of the icons

Next, go to conditional formatting for the column and choose "Icons"

Select field context menu, conditional formatting, IconSelect field context menu, conditional formatting, Icon

In the following menu, choose Format by: Field value. Then in Based on field, find your "Icon URL" in your data model.

Dialog box that lets you set the icon based on a column in your data modelDialog box that lets you set the icon based on a column in your data model

Now behold: Icons in your table!

Icons in table. Icons shown here are from this forum's badgesIcons in table. Icons shown here are from this forum's badges

Advanced - Authentication

Let's say, your icons are stored in a place you need to authenticate in order to view them.

In this sample I will use Dev ops, but you are free to use any data source that produces images.

 

Start by loading the url of the image into your table. E.g. for dev ops you would:

 

 

let
    Source = VSTS.Contents("https://dev.azure.com/<<<Your account>>>/<<<Your project>>>/_apis/wit/workitemtypes?" & apiVersion),
    #"Imported JSON" = Json.Document(Source,65001),
    value = #"Imported JSON"[value],
    Custom1 = Table.FromRecords(value),
    #"Removed Columns2" = Table.RemoveColumns(Custom1,{"url"}),
    #"Expanded icon" = Table.ExpandRecordColumn(#"Removed Columns2", "icon", {"url"}, {"url"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded icon",{"referenceName", "description", "isDisabled", "xmlForm", "fields", "fieldInstances", "transitions", "states"})
in
   #"Removed Columns"

 

 

Next you will need to download those images into Power Bi's data model.

 

 

    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Imagedata", each VSTS.Contents([url])),

 

 

Finally, and here is the fun part, you will ned to encode the image into a data url:

 

 

    #"Invoked Custom Function" = Table.AddColumn(#"Added Custom", "encodeImage", each "data&colon;image/svg+xml;base64," & Binary.ToText([Imagedata], BinaryEncoding.Base64)),

 

 

(replace &colon; with : )

 

Note, you may need to change the image type to the appropiate type. Here it is "svg+xml", but you may want to use "png"

 

Next, simply follow the steps outlined in adding the icons to your table above, possibly adding a relationship to the table along with some additional columns, and you should  end up with something that looks like:

image.png

Comments

@MikeAinOzyou need to base64 encode them:

 

= Table.AddColumn(<<Previous step>>, "encodeImage", each "data&colon;image/svg+xml;base64," & Binary.ToText([Imagedata], BinaryEncoding.Base64)),

 

I had to convert my SVG to binary so I did this to extract the SVG, convert to binary and then convert back to base64 text:

= Table.AddColumn(#"Removed Columns", "EncodedSvg", each "data&colon;image/svg+xml;Base64," & Binary.ToText(Text.ToBinary(Text.AfterDelimiter([Image],",")),

 

and still got a broken image. 

 

Do you think I should convert the SVG to an image? I was thinking Python

Change:

image/svg+xml;Base64

to

image/svg+xml;base64

This was AWESOME. Thanks so much @artemus for writing this up. It was exactly what I wanted to do (including the work item icons from VSTS). 

 

One thing I had to do was change "&colon;" to literally a colon ":". I'm not sure if the &colon; is something on the website? Otherwise it worked great.

@jevansaks Your welcome. Yes, the colon gets replaced due to a forum bug. There is nothing I can do about it.

 

Also, in addition to work icons, you can also add people pictures and put that inside the table as well (although this may make the table rows taller).