End-to-End Example for Adding Icons to Your Power ...

TimoRiikonen · ‎03-02-2020

End-to-End Example for Adding Icons to Your Power BI Report

You can download the example Power BI solution from the end of this blog post.

Upload images

If you have thousands of pictures in database, you may consider using Filestream. In this example, I haven’t used Filestream additions.
Copy your images to a folder such as C:\SQLData\Import
- My example images are country flags, which I downloaded one by one from https://www.countryflags.com/ (choose icon and 32 pixel size).
Execute this SQL script against your database. This script includes all of the preparation tasks to-be-executed once only:

USE master

GO

-- This is needed only if you use FILESTREAM:

-- EXEC sp_configure filestream_access_level, 2

-- RECONFIGURE

-- This is needed only if you use script to read file *names*

-- To allow advanced options to be changed.

EXEC sp_configure 'show advanced options', 1;

GO

-- To update the currently configured value for advanced options.

RECONFIGURE;

GO

-- To enable the feature.

EXEC sp_configure 'xp_cmdshell', 1;

GO

-- To update the currently configured value for this feature.

RECONFIGURE;

GO

-- Create table for uploading files

USE AdventureWorks2017

GO

CREATE TABLE [dbo].[Pictures](

[pictureName] [nvarchar](40) NOT NULL,

[picFileName] [nvarchar](100) NULL,

[PictureData] [varbinary](max) NULL,

PRIMARY KEY CLUSTERED

(

[pictureName] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

-- Create upload procedure for uploading files to DB, see more from https://www.mssqltips.com/sqlservertip/4963/simple-image-import-and-export-using-tsql-for-sql-server...

CREATE PROCEDURE dbo.usp_ImportImage (

@PicName NVARCHAR (100)

, @ImageFolderPath NVARCHAR (1000)

, @Filename NVARCHAR (1000)

)

AS

BEGIN

DECLARE @Path2OutFile NVARCHAR (2000);

DECLARE @tsql NVARCHAR (2000);

SET NOCOUNT ON

SET @Path2OutFile = CONCAT (

@ImageFolderPath

,'\'

, @Filename

);

SET @tsql = 'insert into Pictures (pictureName, picFileName, PictureData) ' +

' SELECT ' + '''' + @PicName + '''' + ',' + '''' + @Filename + '''' + ', * ' +

'FROM Openrowset( Bulk ' + '''' + @Path2OutFile + '''' + ', Single_Blob) as img'

EXEC (@tsql)

SET NOCOUNT OFF

END

GO

If you are doing just a demo, then you can insert images one by one:

exec dbo.usp_ImportImage 'United States','C:\SqlData\Import','united-states-of-america-flag-icon-32.png'

exec dbo.usp_ImportImage 'Canada','C:\SqlData\Import','canada-flag-icon-32.png'

exec dbo.usp_ImportImage 'United Kingdom','C:\SqlData\Import','united-kingdom-flag-icon-32.png'

But if you have many images (or any binary files) to upload, execute this script:

USE AdventureWorks2017

GO

SET NOCOUNT ON

-- Deletes everything from the table!

TRUNCATE TABLE [dbo].[Pictures]

-- 1 - Variable declaration

DECLARE @dbName sysname

DECLARE @importPath NVARCHAR(500)

DECLARE @cmd NVARCHAR(500)

DECLARE @fileList TABLE (importFilename NVARCHAR(255))

DECLARE @importFile NVARCHAR(500)

-- 2 - Initialize variables

SET @importPath = 'C:\SqlData\Import\'

-- 3 - get list of files

SET @cmd = 'DIR /b "' + @importPath + '"'

INSERT INTO @fileList(importFilename)

EXEC master.sys.xp_cmdshell @cmd

-- 4 - Upload image

DECLARE importFiles CURSOR FOR

SELECT importFilename

FROM @fileList

WHERE importFilename IS NOT NULL -- importFilename LIKE '%.PNG'

OPEN importFiles

-- Loop through all the files

FETCH NEXT FROM importFiles INTO @importFile

WHILE @@FETCH_STATUS = 0

BEGIN

-- SET @cmd = 'EXEC dbo.usp_ImportImage ''' + LEFT(@importFile, CHARINDEX('.', @importFile)-1)

SET @cmd = 'EXEC dbo.usp_ImportImage ''' + LEFT(@importFile, CHARINDEX('-flag', @importFile)-1)

+ ''', ''C:\SqlData\Import'', ''' + @importFile + ''''

PRINT @cmd

FETCH NEXT FROM importFiles INTO @importFile

END

-- SELECT * FROM [dbo].[Pictures]

Result from that should be several lines of commands. Copy them to another query window and execute them.

Create report with icons

If you import this new table to Power BI, you should see something similar to this. If you can’t find the new table press Refresh All.

You can’t use binary files directly as icons, but you need to convert them as instructed in https://community.powerbi.com/t5/Community-Blog/Include-Custom-Icons-in-Your-Tables/bc-p/951035#M193... by @artemus
Also, we need to fine-tune the PictureName column, so that file names fit with the DB data.
Here is the full Power Query script for importing, enhancing and conversion. Insert it in advanced editor:

let

Source = Sql.Database("localhost", "adventureworks2017"),

dbo_Pictures = Source{[Schema="dbo",Item="Pictures"]}[Data],

dbo_Pictures2 = Table.AddColumn(dbo_Pictures, "encodedImage", each "data&colon;image/png;base64," & Binary.ToText([PictureData], BinaryEncoding.Base64)),

#"Replaced Value" = Table.ReplaceValue(dbo_Pictures2,"-"," ",Replacer.ReplaceText,{"pictureName"}),

#"Capitalized Each Word" = Table.TransformColumns(#"Replaced Value",{{"pictureName", Text.Proper, type text}})

in

#"Capitalized Each Word"

Let’s go to the DAX side to define the Model. Drag the relationship, in this case PictureName to Country Region Name. By default, this relationship is created as a single direction, but that means that countries without icon will be blanked. So, let’s change the relationship to both of the directions:

You can define icons on table and matrix, but at least for now (2020-02), you can’t do that to any (?) other chart because they don’t have a conditional formatting feature for the values.
Let’s make a simple UI table where we table First Name and Country [Region Name] from sales data table
As we have two fields in values, but we want only one of them to have the icon, do not go to Format tab. Instead choose the arrow from the Country Region Name field and select Conditional formatting -- > Icons:

Choose Format by Field value and select the encoded image column:

Thanks to the model connection, Power BI knows which image to use. Because the connection is bi-directional, values without icon won’t get blanked. As I haven’t imported all flag images, some of the icons are missing, as expected from the result: