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#M1935 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: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:
Advocate V